Creating Records

This module covers joins in the library management application. A join combines data from several entities so you can query information that spans related records. After working through it, you will be able to write Inner Joins and Outer Joins to get the data you need.

A join merges records from two or more entities through a column they share. You need one whenever the data you want lives in more than one table. In a library system, for example, joining the Book and Author entities tells you which author wrote each book.

This module covers two join types:

• Inner Join: returns only the records whose values match in both entities.
• Outer Join: returns every record from the first entity, including records with no match in the other entity. Where no match exists, the missing side is null (Rell's @outer is a left outer join).

Start with a few basic joins in Rell. Add the following examples to the queries.rell file in the /src/library_management directory:

// Query to retrieve books along with their authors using an inner joinquery get_books_with_authors() {    return (book, book_author, author) @* {        book == book_author.book,        author == book_author.author    } (book.title, author.author_name);}

An Inner Join returns only the records that have matching values in every entity it joins. The query above lists each book together with its authors. It joins book, book_author, and author, matching each book to the book field in book_author and each author to the author field in book_author. As a result, books without an author are left out.

An Outer Join returns every record from the first entity, even when a record has no match in the other entity. The side without a match is filled with NULL.

query get_libraries_with_shelves() {    return (l: library, @outer s: shelf @* { s.library == l }) @* {} ( @group l.library_name, @list shelves = s?.number );}

This query lists every library with its shelves. Because it is an Outer Join, libraries that have no shelves still appear in the result. The ? operator marks the match as optional, so a library without shelves gets NULL.

Next, you apply these joins in larger queries.

When you need detailed results, you can combine several joins in one query, as in this example:

// Query to retrieve books, their authors, and the libraries they are located inquery get_books_authors_libraries() {    return (book, book_author, author, shelf, library) @* {        book == book_author.book,        author == book_author.author,        book.shelf == shelf,        shelf.library == library    } (book.title, author.author_name, library.library_name);}

This query returns books, their authors, and the libraries that hold them. It combines books with authors and still includes library information when a book is not linked to any library.

The next query uses an Inner Join to return members and the books they currently have on loan.

// Query to retrieve members along with the books they have on loan using an inner joinquery get_members_with_loans() {    return (member, loan, book) @* {        member == loan.member,        book == loan.book    } (member.member_name, book.title);}

It joins member, loan, and book, matching each member to the member in loan and each book to the book in loan. Only members who currently have borrowed books appear in the result.

The following query returns books on loan, the members who borrowed them, and the authors of those books.

// Query to retrieve books on loan along with the borrowing members and the authors of the booksquery get_books_on_loan() {    return (book, loan, member, book_author, author) @* {        book == loan.book,        member == loan.member,        book == book_author.book,        author == book_author.author    } (book.title, member.member_name, author.author_name);}

To query live records from the database for demonstration, start the node locally with this command:

chr node start

If the node is already running, use this instead:

chr node update

Then run the command below, replacing the <operation> placeholder with the name of an existing query, such as get_books_on_loan:

chr query <operation>// Examplechr query get_books_on_loan

For grouping query results by a specific key, see this guide and the Chromia docs.

The books-on-loan query above joins the book, loan, member, book_author, and author entities through their relationships. The result contains each book currently on loan, the member who borrowed it, and its authors.

Joins on large datasets can be slow, so optimize your queries for performance. Keep these points in mind:

You have now seen the basics of joins in Rell, focusing on Inner Joins and Outer Joins. You used them to combine data from multiple entities and learned how keys and indexes help join performance. You can now write efficient queries that pull data from several entities in your library management system.