SELECT statement

To read records from the database, you will usually reach for the at-operator, @.

FROM        CARDINALITY  WHERE         WHAT  TAILentity_name @            { condition } ()    ;

An at-expression has five parts: FROM, CARDINALITY, WHERE, WHAT, and TAIL.

• FROM: the table you query. It works like FROM in SQL.
• CARDINALITY: how many results you expect. If the actual result count doesn't satisfy it, the query fails.
  • @ exactly one result
  • @? zero or one result
  • @+ one or more results
  • @* zero or many results
• WHERE: a filter, comparable to the WHERE clause in SQL.
• WHAT: the values the query returns. List one or more attributes, or leave it empty to get the entity reference (rowid). This part also lets you sort, group, or omit fields.
• TAIL: extra SQL-style clauses for multi-row results, such as limit or offset.

Implicit select statements

Some SELECTs run without being obvious in the code. Reading an attribute from an entity is one such case.

val attribute = entity_instance.attribute;

That line produces this SQL query:

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

Written as an at-expression, the same database roundtrip becomes visible:

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

Both forms map to the same SQL.

Calling .to_struct() on an entity fetches all of its attributes and gives you an in-memory copy. Keep in mind that this also costs a database roundtrip, so the following statements do the same thing:

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

Select statement example

Because every attribute access can trigger a SELECT, heavy use of it adds up quickly. Consider this 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;}

It looks harmless, but it makes four database roundtrips, one per attribute. Call it inside a loop and the cost multiplies:

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

Now the roundtrip count grows linearly with the number of rows in the table.

Change the signature to function is_mansion(house: struct<house>) and compare. All the required data now comes back in one database statement:

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

Practical example

Open queries.rell in the src/main directory and add the query below.

It returns every house that counts as a mansion:

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

Run the get_all_mansion query from the CLI with the chr command:

chr query get_all_mansion

You should see output like this:

[  [    "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  ]]