Filter and sort query results

This part of the tutorial shows you how to write structured queries in Rell that filter and sort data, so users get the results they need. Rell's relational expressions all share one consistent shape, which keeps data retrieval predictable.

Every structured query in Rell is built from five parts:

1. FROM: the data source. You can join and filter it.
2. CARDINALITY: how many results are expected, set with an operator such as @, @*, @+, or @?.
3. WHERE: the conditions and joins that filter the data.
4. WHAT: the projection, aggregation, and sort order.
5. LIMIT: the maximum number of elements returned.

Rell evaluates a query logically in this order, so the execution sequence is always clear.

Filtering reviews by rating

Start with a new query, get_reviews_by_rating, that returns only the reviews with a given rating. The full query is below, followed by a breakdown:

query get_reviews_by_rating(rating: integer) {  return book_review @* { .rating == rating } (    .book.to_struct(),    .reviewer_name,    .review,    .rating  );}

The parts of this query map as follows:

• FROM: book_review
• CARDINALITY: @*
• WHERE: { .rating == rating }
• WHAT: ( .book.to_struct(), .reviewer_name, .review, .rating )
  (Calling .book.to_struct() reads a referenced entity, which causes an implicit join.)

Testing filtered reviews

To confirm the query returns only reviews with the requested rating, write a test for it in your test module.

Updating imports

Add the new query to the imports in your test file:

import main.{ book, create_book, get_all_books, book_review, create_book_review, get_reviews_by_rating };

Adding a test function

Then add this test function to check the query's behavior:

function test_get_reviews_by_rating() {    rell.test.tx()        .op(create_book("123", "Book1", "Author1"))        .op(create_book_review("123", "Reviewer1", "ReviewText1", 4))        .op(create_book_review("123", "Reviewer2", "ReviewText2", 5))        .op(create_book_review("123", "Reviewer3", "ReviewText3", 4))        .op(create_book_review("123", "Reviewer4", "ReviewText4", 1))        .sign(book_keeper)        .run();    val reviews = get_reviews_by_rating(4);    assert_equals(reviews.size(), 2);}

The test creates a book, posts four reviews with varying ratings, queries for reviews rated 4, and asserts that exactly two come back.

Sorting reviews by rating

Next, add sorting. You'll modify the existing get_all_reviews_for_book query so it orders reviews by rating. The version below sorts in ascending order; for descending order, use @sort_desc instead:

query get_all_reviews_for_book(isbn: text) {  require(book @? { .isbn == isbn }, "Book with ISBN %s not found".format(isbn));  val reviews = book_review @* { .book.isbn == isbn } (    @omit @sort_desc .rating, // Sort by rating, but omit this field in the result.    book_review_dto(      book = .book.to_struct(),      .reviewer_name,      .review,      .rating    )  );  return reviews;}

Updating imports

Include this query in your test file's imports:

import main.{ book, create_book, get_all_books, book_review, create_book_review, get_reviews_by_rating, get_all_reviews_for_book };

Adding a test function

Add this test function to confirm the sort order:

function test_get_sorted_reviews_by_rating() {    rell.test.tx()        .op(create_book("123", "Book1", "Author1"))        .op(create_book_review("123", "Reviewer1", "ReviewText1", 5))        .op(create_book_review("123", "Reviewer2", "ReviewText2", 2))        .op(create_book_review("123", "Reviewer3", "ReviewText3", 4))        .sign(book_keeper)        .run();    val reviews = get_all_reviews_for_book("123");    assert_equals(reviews.size(), 3);    assert_equals(reviews[0].rating, 5);    assert_equals(reviews[1].rating, 4);    assert_equals(reviews[2].rating, 2);}

The test creates a book, posts three reviews with different ratings, queries for the reviews in ascending rating order, and asserts that they arrive in the correct sequence.

Running the tests

Run this command to execute the tests and check your queries:

chr test

Your application now supports filtering and sorting: an administrator can add books, any user can post reviews of them, and you can query books and reviews with filtered, sorted results.