INNER JOIN statement

To join two tables in Rell, list every table you want to join in the FROM part of the at-expression, then state how they relate in the WHERE part:

function join_example() {    val res = (house, street) @* { street == house.street };}

This Rell code maps to the following SQL query:

SELECT A00."rowid", A01."rowid"    FROM "street" A00, "house" A01    INNER JOIN "street" A02 ON A01."street" = A02."rowid"    WHERE A00."rowid" = A02."rowid"    ORDER BY A00."rowid", A01."rowid"

Implicit statements

Here, house has a foreign key constraint on street. This means you can reach street straight from a house instance:

val street = house_instance.street;

This access also produces a join:

SELECT A01."rowid"    FROM "house" A00    INNER JOIN "street" A01 ON A00."street" = A01."rowid"    WHERE A00."rowid" = ?    ORDER BY A00."rowid"

The same applies whenever you touch street while working with house. Any such access results in a join.

house @ { .street.address == "FakeStreet" };house @ {} (.street);house_instance.to_struct();

A join on a foreign key constraint is usually not expensive, but you should still know which Rell statements generate joins. The previous example used struct<house> in the function expression. When house references several other tables, calling to_struct() performs one join per reference. The next example adds a few references to illustrate this:

entity garage {}entity house_key {}entity house {    ...    garage;    house_key;}

Avoiding unnecessary joins

Suppose you reuse the earlier construct:

val houses = house @* {} ($.to_struct());

The query now joins both the garage and house_key entities. If the function doesn't need them, select the required fields explicitly in the at-expression:

struct house_info {    number_of_rooms: integer;    number_of_floors: integer;    floor_area: integer;}val houses = house @* { } ( house_info( .number_of_rooms, .number_of_floors, .floor_area ) );

Changing the function signature to is_mansion(house: house_info) lets you control exactly which values are fetched from the database.

Practical example

The following queries return records filtered according to each query's criteria.

Add get_house_with_streets to the queries.rell file in the src/main directory:

query get_houses_with_streets() = (house, street) @* { street == house.street } ( street = .street.address, .number );

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

chr query get_houses_with_streets

[  [    "number": 23,    "street": "Drottninggatan"  ],  [    "number": 24,    "street": "Kungsgatan"  ],  [    "number": 30,    "street": "Kungsgatan"  ]]

Next, add the get_houses_with_specific_street query to queries.rell in the src/main folder.

query get_houses_with_specific_street(street_address: text) = house @* { .street.address == street_address } ( .number, street = .street.address );

Run get_houses_with_specific_street with the chr command, passing the street argument:

chr query get_houses_with_specific_street "Drottninggatan"

You get the following response:

[  [    "number": 23,    "street": "Drottninggatan"  ]]

To fetch details for specific houses, such as the number of rooms, the number of floors, and the total area, use the get_houses_info query.

Add this code to queries.rell in the src/main directory:

query get_houses_info() = house @* { } ( house_info( .number_of_rooms, .number_of_floors, .floor_area ) );

chr query get_houses_info

The query returns:

[  [    "floor_area": 4,    "number_of_floors": 4,    "number_of_rooms": 5  ],  [    "floor_area": 500,    "number_of_floors": 2,    "number_of_rooms": 12  ],  [    "floor_area": 700,    "number_of_floors": 3,    "number_of_rooms": 15  ]]