Associate objects by a key

In this guide, we will build a simple function for creating a map that collects a structure based on a specific key. This is a standard pattern when working with many-to-one relations.

Say that you have, for example, the entity relations:

entity house {
    key id: integer;
    color: text;
}

entity street {
    key address: text;
}

entity street_house {
    key house;
    index street;
}

For this relation, each house is unique and lives on a street, but each street can contain several houses. If we want to create a query that returns all houses grouped by their street, we'd like to get the following signature:

query get_houses_by_street(): map<text, list<struct<house>>>

Each entry in the map has the address as a key and a list of house-structs as values. Performing a database query using the at-operator, we can at most get a list<text, struct<house>>:

val house_list: list<(text, struct<house>)> = street_house @* {} ( .street.address, .house.to_struct() );

How do we create a map for this structure? Luckily, rell has a few annotations to aggregate the results into collections. @group is used to specify what we want to group with, and @list or @set can specify the target collection type.

We can now collect the results in a slightly better structure:

val house_streets: list<(text, list<struct<house>>)> =
  street_house @* {} ( @group .street.address, @list .house.to_struct() );

The @group annotation tells the ORM that we want to group the results using the street address as the key, and the @list annotation collects all houses on the same street.

But we can directly convert the resulting type into a map using another annotation provided by Rell ORM, called @map:

val houses_by_street = house_streets @ {} ( @map $ );

This is another at-expression that works on a collection. In the what-clause, we use @map to treat each entry as a map entry.

Using this approach, we can optimize the database query for obtaining the results and convert them into a map structure using fewer lines of code and in an optimized way.