SELECT Statement

The most common way to select something 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. 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 );

which 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

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