Lesson 8.4: Sorting and pagination

Sorting

The results of any query can be sorted by adding the sort modifier to the query. The following query retrieves the list of every book’s title and page count, and sorts by page count in ascending order, and then takes the shortest 5 books with a limit:

match $book isa book, has page-count $count;
sort $count;
limit 5;
fetch { "title": $book.title, "page-count": $book.page-count };
{
  "title": "The Iron Giant",
  "page-count": 79
}
{
  "title": "The Motorcycle Diaries: A Journey Around South America",
  "page-count": 160
}
{
  "title": "Physical Principles of Electron Microscopy: An Introduction to TEM, SEM, and AEM",
  "page-count": 196
}
{
  "page-count": 199,
  "title": "Interpretation of Electron Diffraction Patterns"
}
{
  "page-count": 215,
  "title": "The Hitchhiker's Guide to the Galaxy"
}

The sort modifier takes a variable as an argument, denoting the variable to sort on. The results are sorted in ascending order. In order to sort in descending order, we can add append the keyword desc to the modifier as follows to get the 5 longest books:

match $book isa book, has page-count $count;
sort $count desc;
limit 5;
fetch { "title": $book.title, "page-count": $book.page-count };
{
  "title": "The Complete Calvin and Hobbes",
  "page-count": 1451
}
{
  "title": "Classical Mythology",
  "page-count": 820
}
{
  "title": "The Odyssey",
  "page-count": 656
}
{
  "title": "Dune",
  "page-count": 624
}
{
  "title": "One Hundred Years of Solitude",
  "page-count": 458
}

Though sorting is always in ascending order by default, we can specify this explicitly using the keyword asc in the same way as desc.

The ordering of strings is determined according lexicographic ordering.

Pagination

We can do simple pagination the sorted results of any query by combining the limit and offset modifiers. In the following example, we add a limit modifier to the previous query to retrieve 5 to 10th books with the lowest page counts.

match $book isa book, has page-count $count;
sort $count;
offset 5;
limit 5;
fetch { "title": $book.title, "page-count": $book.page-count };
{
  "page-count": 240,
  "title": "The Mummies of Urumchi"
}
{
  "page-count": 260,
  "title": "Business Secrets of The Pharoahs"
}
{
  "title": "To Kill a Mockingbird",
  "page-count": 281
}
{
  "title": "Pride and Prejudice",
  "page-count": 295
}
{
  "title": "Under the Black Flag: The Romance and the Reality of Life Among the Pirates",
  "page-count": 296
}

The sort, limit, and offset modifiers can all be used individually or in any combination. They apply in the order they are written!

Note: offset/limit pagination is easy, but it is not efficient as the offset computes, then discards values! More efficient pagination uses a known, ordered attribute value and fetches pages at a time by restricting the search to answers with value greater than the last value: match <pattern>; $attribute > [last-returned-value].

Exercise

Write a Fetch query to retrieve the titles of the five most recently published books in the database.

You may find it useful to refer to the bookstore’s schema.

Schema 
define

entity book @abstract,
    owns isbn @card(0..2),
    owns isbn-13 @key,
    owns isbn-10 @unique,
    owns title,
    owns page-count,
    owns genre @card(0..),
    owns price,
    plays contribution:work,
    plays publishing:published,
    plays promotion-inclusion:item,
    plays order-line:item,
    plays rating:rated,
    plays recommendation:recommended;

entity hardback sub book,
    owns stock;

entity paperback sub book,
    owns stock;

entity ebook sub book;

entity contributor,
    owns name,
    plays contribution:contributor,
    plays authoring:author,
    plays editing:editor,
    plays illustrating:illustrator;

entity company @abstract,
    owns name;

entity publisher sub company,
    plays publishing:publisher;

entity courier sub company,
    plays delivery:deliverer;

entity publication,
    owns year,
    plays publishing:publication,
    plays locating:located;

entity user,
    owns id @key,
    owns name,
    owns birth-date,
    plays action-execution:executor,
    plays locating:located,
    plays recommendation:recipient;

entity order,
    owns id @key,
    owns status,
    plays order-line:order,
    plays action-execution:action,
    plays delivery:delivered;

entity promotion,
    owns code @key,
    owns name,
    owns start-timestamp,
    owns end-timestamp,
    plays promotion-inclusion:promotion;

entity review,
    owns id @key,
    owns score,
    owns verified,
    plays rating:review,
    plays action-execution:action;

entity login,
    owns success,
    plays action-execution:action;

entity address,
    owns street,
    plays delivery:destination,
    plays locating:located;

entity place @abstract,
    owns name,
    plays locating:located,
    plays locating:location;

entity city sub place;

entity state sub place;

entity country sub place;

relation contribution,
    relates contributor,
    relates work;

relation authoring sub contribution,
    relates author as contributor;

relation editing sub contribution,
    relates editor as contributor;

relation illustrating sub contribution,
    relates illustrator as contributor;

relation publishing,
    relates publisher,
    relates published,
    relates publication;

relation promotion-inclusion,
    relates promotion,
    relates item,
    owns discount;

relation order-line,
    relates order,
    relates item,
    owns quantity,
    owns price;

relation rating,
    relates review,
    relates rated;

relation action-execution,
    relates action,
    relates executor,
    owns timestamp;

relation delivery,
    relates deliverer,
    relates delivered,
    relates destination;

relation locating,
    relates located,
    relates location;

relation recommendation,
    relates recommended,
    relates recipient;

attribute isbn @abstract, value string;
attribute isbn-13 sub isbn;
attribute isbn-10 sub isbn;
attribute title, value string;
attribute page-count, value integer;
attribute genre, value string;
attribute stock, value integer;
attribute price, value double;
attribute discount, value double;
attribute id, value string;
attribute code, value string;
attribute name, value string;
attribute birth-date, value datetime;
attribute street, value string;
attribute year, value integer;
attribute quantity, value integer;
attribute score, value integer;
attribute verified, value boolean;
attribute timestamp, value datetime;
attribute start-timestamp, value datetime;
attribute end-timestamp, value datetime;
attribute status, value string @regex("^(paid|dispatched|delivered|returned|canceled)$");
attribute success, value boolean;

# TODO: Change to check
fun is_review_verified_by_purchase($review: review) -> { order }:
  match
    ($review, $product) isa rating;
    ($order, $product) isa order-line;
    ($user, $review) isa action-execution, has timestamp $review-time;
    ($user, $order) isa action-execution, has timestamp $order-time;
    $review-time > $order-time;
  return { $order };

fun book_recommendations_for($user: user) -> {book}:
  match
    $new-book isa book;
    {
        let $new-book in book_recommendations_by_author($user);
    } or {
        let $new-book in book_recommendations_by_genre($user);
    };
  return { $new-book };

fun book_recommendations_by_genre($user: user) -> { book }:
match
    $user isa user;
    $liked-book isa book;
    {
        ($user, $order-for-liked) isa action-execution;
        ($order-for-liked, $liked-book) isa order-line;
    } or {
        ($user, $review-for-liked) isa action-execution;
        ($review-for-liked, $liked-book) isa rating;
        $review-for-liked has score >= 7;
    };
    $new-book isa book;
    not { {
        ($user, $order-for-new) isa action-execution;
        ($order-for-new, $new-book) isa order-line;
    } or {
        ($user, $review-for-new) isa action-execution;
        ($review-for-new, $new-book) isa rating;
    }; };
    $liked-book has genre $shared-genre;
    $new-book has genre $shared-genre;
    not { {
        $shared-genre == "fiction";
    } or {
        $shared-genre == "nonfiction";
    }; };
  return { $new-book };

fun book_recommendations_by_author($user: user) -> { book }:
  match
    $user isa user;
    $liked-book isa book;
    {
        ($user, $order-for-liked) isa action-execution;
        ($order-for-liked, $liked-book) isa order-line;
    } or {
        ($user, $review-for-liked) isa action-execution;
        ($review-for-liked, $liked-book) isa rating;
        $review-for-liked has score >= 7;
    };
    $new-book isa book;
    not { {
        ($user, $order-for-new) isa action-execution;
        ($order-for-new, $new-book) isa order-line;
    } or {
        ($user, $review-for-new) isa action-execution;
        ($review-for-new, $new-book) isa rating;
    }; };
    ($liked-book, $shared-author) isa authoring;
    ($new-book, $shared-author) isa authoring;
  return { $new-book };

fun order_line_best_price($line: order-line) -> { double }:
  match
    ($order) isa action-execution, has timestamp $order-time;
    $line isa order-line, links ($order, $item);
    $item has price $retail-price;
    let $time_value = $order-time;
    let $best-discount = best_discount_for_item($item, $time_value);
    let $discounted-price = round(100 * $retail-price * (1 - $best-discount)) / 100;
    $line has quantity $quantity;
    let $line-total = $quantity * $discounted-price;
  return { $line-total };

fun best_discount_for_item($item: book, $order-time: datetime) -> double:
  match
    {
        $inclusion isa promotion-inclusion,
            links ($promotion, $item),
            has discount $discount-attr;
        $promotion has start-timestamp <= $order-time,
            has end-timestamp >= $order-time;
        let $discount = $discount-attr;
    } or {
        let $discount = 0.0; # default
    };
return max($discount);

fun transitive_places($place: place) -> { place }:
  match
    {
      locating (located: $place, location: $parent);
    } or {
      locating (located: $place, location: $middle);
      let $parent in transitive_places($middle);
    };
  return { $parent };
Sample solution 
match
$book isa book;
$publication isa publication, has year $year;
publishing ($book, $publication);
sort $year desc;
limit 5;
fetch { "title": $book.title };