Subqueries

In this lesson, we will explore how to use nested expressions in Rell to create efficient database queries. We will learn how to use nested expressions in the exists() and empty() functions, as well as the in operator. Nested expressions allow you to create more complex and efficient database queries, significantly improving the performance and functionality of your Rell queries.

Subqueries are used to perform a query within another query to refine search results. For example, if you want to find customers who have placed orders totaling more than $500, you might first use a subquery to calculate the total spent by each customer. This subquery returns a specific result, such as an array of objects containing customer details.

Then, the main query will use this information to list only those customers who meet the criteria. So, the subquery identifies customers with high spending, and the main query retrieves their details.

Adding new records

In order to illustrate the subquery mechanism, we must create two entities, customer and order, in the src/main/entities.rell file. In this example, we will use these entities to:

1. Find all customers who placed at least one order.

2. Find customers who have not yet placed any orders.

3. Find customers who have purchased a specific product.

Below is the code for creating the customer and order entities:

src/main/entities.rell

entity customer {
  name: text;
  city: text;
  email: text;
}

entity order {
  order_id: text;
  customer_email: text;
  product: text;
}

Next, we need two operations to create customer and order records. Add the following code to the src/main/operations.rell file:

src/main/operations.rell

operation create_customer(name: text, city: text, email: text) {
    create customer( name, city, email );
}

operation create_order(order_id: text, customer_email: text, product: text) {
    create order( order_id, customer_email, product );
}

For demonstration purposes, you can run the Chromia node locally to add the new records. If you are experimenting, it's recommended to run the node with the --wipe flag to remove all currently stored records.

chr node start --wipe

Then, you can invoke your operations to add new records. Let's create three customer records by using the create_customer operation; run the following commands sequentially in the terminal:

chr tx create_customer Ann Sydney ann@mail.com
chr tx create_customer Denis London denis@mail.com
chr tx create_customer Lily Rome lily@mail.com

Then, to add order records to the database using the create_order operation, run the following commands sequentially in the terminal:

chr tx create_order "'1'" ann@mail.com phone
chr tx create_order "'2'" lily@mail.com tv

After adding the new records, you can proceed with running subqueries.

Running subqueries

The Rell language includes some built-in global functions in the System Library that can help create a subquery. There are two functions and an operator for performing a subquery:

1. exists() - This function checks if a value exists. It returns true if a value exists; otherwise, it returns false. It expects a value or a collection of values.
2. empty() - This function identifies if a value is null or an empty collection. It returns true if a value/collection is null; otherwise, it returns false.
3. in - This operator verifies if a specific element exists in a collection. It returns true if a value exists; otherwise, it returns false.

Example of using exists()

The following subquery finds all customers who have placed at least one order. In this example, the exists function is invoked as a subquery, which returns a boolean value indicating the presence or absence of an element or a collection of elements. If it returns true, the main query is invoked, returning a collection of customers.

If it returns true, the main query is invoked, returning a collection of customers.

To implement this example, add the following code to the src/main/queries.rell file:

src/main/queries.rell

query get_customers_with_orders() {
    return customer @* {
        exists(order @* { .customer_email == customer.email })
    } ( $.to_struct() );
}

note

To prevent issues with counting records in a nested SQL query, remember to use the @* operator for nested expressions.

After adding the code to the file, you can run this query using the terminal:

chr query get_customers_with_orders

In this example, the query has returned two customer records that have placed at least one order.

[
  [
    "city": "Sydney",
    "email": "ann@mail.com",
    "name": "Ann"
  ],
  [
    "city": "Rome",
    "email": "lily@mail.com",
    "name": "Lily"
  ]
]

Example of using empty()

The following query retrieves all customers who have not placed any orders yet. Add the code below to the src/main/queries.rell file:

src/main/queries.rell

query get_customers_without_orders() {
    return customer @* {
        empty(order @* { .customer_email == customer.email })
    } ( $.to_struct() );
}

You can run this query using the terminal:

chr query get_customers_without_orders

The query has returned one customer record that has not placed any orders yet:

[
  [
    "city": "London",
    "email": "denis@mail.com",
    "name": "Denis"
  ]
]

Example of using in

The following query, get_customers_who_ordered_phone, allows retrieving all customers who have ordered a specific product, such as a phone. Add the code below to the src/main/queries.rell file:

src/main/queries.rell

query get_customers_who_ordered_phone() {
    return customer @* {
        .email in order @* { .product == "phone" } ( order.customer_email )
    } ( $.to_struct() );
}

You can run this query using the terminal:

chr query get_customers_who_ordered_phone

The result of this query is one customer who has placed an order for a phone:

[
  [
    "city": "Sydney",
    "email": "ann@mail.com",
    "name": "Ann"
  ]
]