Subqueries

This lesson shows how nested expressions in Rell help you write efficient database queries. You will nest expressions inside the exists() and empty() functions and use them with the in operator. Nesting lets you build more complex queries that also perform better and do more.

A subquery is a query that runs inside another query to narrow down the results. Suppose you need every customer whose orders add up to more than $500. A subquery can first compute how much each customer has spent and return a specific result, such as an array of objects with customer details.

The outer query then uses that result to list only the customers that match the condition. The subquery finds the high spenders, and the main query fetches their details.

Adding new records

To see subqueries in action, define two entities, customer and order, in src/main/entities.rell. You will use them to:

1. Find every customer with at least one order.

2. Find customers who have no orders yet.

3. Find customers who bought a particular product.

Define the two entities as follows:

entity customer {  name: text;  city: text;  email: text;}entity order {  order_id: text;  customer_email: text;  product: text;}

You also need two operations that create customer and order records. Put this code in 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 );}

To add records for this demo, start a local Chromia node. While experimenting, start it with the --wipe flag so that any previously stored records are deleted.

chr node start --wipe

With the node running, call your operations to insert data. Create three customers with the create_customer operation by running these commands one after another in the terminal:

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

Next, insert orders with the create_order operation by running these commands one after another:

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

Once the data is in place, you can start running subqueries.

Running subqueries

Rell's System Library provides built-in global functions that you can use to build subqueries. Two functions and one operator are available for this:

1. exists() - Takes a value or a collection of values and tells you whether a value is present: true if it is, false if not.
2. empty() - Checks whether a value is null or an empty collection. It gives true when the value is null or the collection is empty, and false otherwise.
3. in - Tests whether a given element is part of a collection, returning true if it is and false if it is not.

Example of using exists()

The query below returns every customer with at least one order. Here, exists acts as the subquery and produces a boolean that shows whether an element or collection of elements is present. Customers for which the result is true are included in the collection the main query returns.

Add this code to src/main/queries.rell:

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

Save the file, then run the query from the terminal:

chr query get_customers_with_orders

The result contains the two customers who have at least one order.

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

Example of using empty()

This query returns the customers who have not placed an order yet. Add it to src/main/queries.rell:

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

Run it from the terminal:

chr query get_customers_without_orders

One customer comes back, the one without any orders:

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

Example of using in

The get_customers_who_ordered_phone, query returns every customer who ordered a particular product, in this case a phone. Add it to src/main/queries.rell:

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

Run it from the terminal:

chr query get_customers_who_ordered_phone

You get one customer, the one who ordered a phone:

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