INNER JOIN statement

Joining two tables is simple in Rell. We do this by putting all the tables we want to join in the FROM part of the at-expression and specify the constraint between them in the WHERE part:

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

The Rell code above will correspond 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

Now, in this scenario, we have a foreign key constraint on street from house, so we can access street directly from an instance of house, for example:

val street = house_instance.street;

This will also correspond to a join statement:

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

Similarly, accessing street in any way when working with house will result in a join.

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

Even though joining a table with a foreign key constraint may not be a heavy join, it is important to remember which Rell statements will create a join. For example, we used the struct<house> construct in the previous example in the function expression. If house has several references to other tables, the to_struct() call will make several joins. For example, let's add some references to the previous example:

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

Avoiding unnecessary joins

If we, in this case, use the same construct as before:

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

This joins both garage and house_key entities. If these are not needed in the function, we should be more explicit 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 ) );

By changing the function's signature to is_mansion(house: house_info), we can optimize which values we get from the database.

tip

Extract all the values you need directly in the at expression to avoid unnecessary joins. It's best to define your own structures when needed.

Practical example

In this example we use a set of queries to retrieve filtered records based on the query specifics.

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

src/main/queries.rell

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

Invoke the get_all_mansion query using the chr command in the CLI:

chr query get_houses_with_streets

result

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

Add the get_houses_with_specific_street query to the queries.rell file stored in the src/main folder.

src/main/queries.rell

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

Invoke the get_houses_with_specific_street query with the street argument using the chr command:

chr query get_houses_with_specific_street "Drottninggatan"

This invocation results in the following response:

result

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

Next, we need to retrieve the specific house information such as number of rooms, floors and the total area by using the get_houses_info query.

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

src/main/queries.rell

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

chr query get_houses_info

The result of this query looks as follows:

result

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