Skip to main content

Creating Records

In this module, we will explore the concept of joins in our library management application. Joins are essential for combining data from multiple entities, allowing us to retrieve comprehensive information that spans across related records. By the end of this module, you will understand how to perform Inner Joins and Outer Joins to query data effectively.

Joins are operations that allow you to combine records from two or more entities based on a related column. This is especially useful when you need to retrieve data that is spread across multiple tables. For example, in a library system, you might want to join the Book and Author entities to find out who wrote which book.

Types of joins covered:

  • Inner Join: Returns records that have matching values in both entities.
  • Outer Join: Returns all records from both entities, including those that do not have a match in the other entity. If there is no match, the result is NULL on the side with no corresponding record.

Let’s start with some basic examples of how to perform joins in Rell. Add the following code examples to the queries.rell file in the /src/library_management directory:

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

The Inner Join retrieves all records that have matching values in both entities involved in the join. This query retrieves all books along with their corresponding authors. It combines the book, book_author, and author entities where the book matches the book in book_author, and the author matches the author in book_author. This ensures that only books with corresponding authors are returned.

The Outer Join retrieves all records from both entities, including those that do not have a match in the other entity. If there is no match, the result is NULL on the side with no corresponding record.

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

This query retrieves all the libraries and their corresponding shelves. It uses an Outer Join to ensure that all libraries are included in the result, even if they do not have any shelves. The ? operator is used to denote optional matches, returning NULL for libraries without shelves.

To put these concepts into practice, let’s see how we can use these joins in more complex queries.

Sometimes, you might need to combine joins to fetch detailed information. Here’s an example that combines joins:

/src/library_management/queries.rell
// Query to retrieve books, their authors, and the libraries they are located in
query 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 retrieves books, their authors, and the libraries they are located in. It uses a more complex query to combine books with authors and include library information even if some books are not associated with a library.

This query retrieves all members and the books they have currently on loan (Inner Join).

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

This query retrieves all members and the books they have on loan. It combines the member, loan, and book entities where the member matches the member in loan, and the book matches the book in loan. This ensures that only members with currently borrowed books are returned.

This query retrieves books on loan, the members who borrowed them, and the authors of those books.

/src/library_management/queries.rell
// Query to retrieve books on loan along with the borrowing members and the authors of the books
query 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);
}

For demonstration purposes, if you want to query the existing records from the database in real time, you have to run the node locally by using the following command:

chr node start

Or if the node is already running:

chr node update

Then run the following command, substituting the <operation> placeholder with the existing operation name for instance get_books_on_loan:

chr query <operation>

// Example
chr query get_books_on_loan

To learn how to effectively group data by a specific key in queries, visit this guide and on Chromia docs.

This query retrieves books that are currently on loan, the members who borrowed them, and the authors of those books. It joins the book, loan, member, book_author, and author entities based on their relationships, ensuring that all relevant information is included in the result.

When performing joins, especially on large datasets, it is important to optimize your queries to ensure efficient performance. Here are a few tips:

tip
  • Indexing: Ensure that fields used in JOIN conditions are indexed. This can significantly improve query performance.
  • Filter early: Apply filters early in your WHERE clause to reduce the number of records that need to be processed.
  • Minimize joins: Try to minimize the number of joins by only including necessary entities.

In this module, we covered the basics of joins in Rell, specifically focusing on Inner Joins and Outer Joins. We discussed how to use these joins to combine data from multiple entities and how to optimize join performance using keys and indexes. With these concepts, you can now write efficient queries that combine data from multiple entities in your library management system.