SELECT statement

The most common way to select a record from the database is by using the @ at-operator.

FROM        CARDINALITY  WHERE         WHAT  TAIL
entity_name @            { condition } ()    ;

The operator is separated into five parts: FROM, CARDINALITY, WHERE, WHAT, and TAIL.

• FROM: represents the table from where to make the query. Same as SQL FROM.
• CARDINALITY: specifies the number of results that are expected from the query. The query will fail if this condition can't be matched.
  • @ exactly one result
  • @? zero or one result
  • @+ more than one result
  • @* zero or many results
• WHERE: represents a filter similar to the WHERE part of an SQL query.
• WHAT: The actual values to retrieve from the query. It can be one or several attributes or left empty to get a reference (rowid) of the entity. Here, you can also   sort, group, or omit fields.
• TAIL: Additional SQL-related statements such as limit or offset that can be used when retrieving multiple results.

tip

The at-operator also works on collection types such as list<T> and set<T> as a powerful replacement for the for loop.

Implicit select statements

There are also cases where SELECTs are performed without the reader noticing. This happens when we access an attribute of an entity.

val attribute = entity_instance.attribute;

This will result in the following SQL query:

SELECT "attribute" FROM "entity_name" WHERE "rowid" = ? ORDER BY "rowid"

To make the database roundtrip explicit, we can form the following at-expression:

val attribute = entity_name @ { $ == entity_instance } ( .attribute );

The expression provided above will correspond to an equivalent SQL expression.

You can use the .to_struct() function to retrieve all attributes from an entity. This will give you an in-memory representation of the entity. However, it is important to note that this will also create a database roundtrip to retrieve the values. This means that the following statements are equivalent:

val struct1 = entity_instance.to_struct();
val struct2 = entity_name @ { $ == entity_instance } (struct<entity_name>(attribute = .attribute, ...));

Select statement example

Based on the above, we see that excessive use of attribute access can lead to many SELECTs. Have a look at the following function:

function is_mansion(house) {
  if (house.floor_area <= 460) return false;
  if (house.number_of_rooms < 10) return false;
  return house.number_of_floors > 1 and house.number_of_floors <= 3;
}

This may seem like a reasonable function, but in reality, this will perform four database roundtrips, getting the attributes one by one. Putting this in a loop makes the situation even worse:

val houses = house @* {};
for (house in houses) {
  if (is_mansion(house)) print("Found a mansion");
}

The number of database roundtrips will increase linearly with the size of the database table.

danger

This means that passing entity references to functions as arguments is really dangerous as it can lead to a lot of unnecessary database roundtrips.

Consider the difference when we change the signature to function is_mansion(house: struct<house>). With this change, we can retrieve all the data we need in a single database statement:

val houses = house @* {} ($.to_struct());
for (house in houses) {
  if (is_mansion(house)) print("Found a mansion");
}

tip

Don't pass entity references in functions, as it might lead to additional database roundtrips that can grow uncontrollably in the worst case.

Practical example

Add the following query to the queries.rell file stored in the src/main directory.

This query retrieves all house records:

src/main/queries.rell

query get_all_mansion() = house @* { .floor_area >= 406, .number_of_rooms > 10, .number_of_floors > 1 } ( $.to_struct() );

You can invoke the get_all_mansion query using the chr command in the CLI:

chr query get_all_mansion

The result of this query looks as follows:

result

[
  [
    "floor_area": 500,
    "number": 24,
    "number_of_floors": 2,
    "number_of_rooms": 12,
    "street": 2
  ],
  [
    "floor_area": 700,
    "number": 30,
    "number_of_floors": 3,
    "number_of_rooms": 15,
    "street": 2
  ]
]