New ACM paper, free-tier cloud, and open-source license

Lesson 8.2: Subqueries

Grouping results

In the previous lesson, we saw how we can modify our query to retrieve attributes individually or grouped by owner. In this lesson, we will explore how we can group results in a generalised way using subqueries.

In Lesson 7.6, we encountered the following Fetch query, which was intended to list all books and the promotions they are in, but instead lists only books that are in promotions.

match
$book isa book;
$promotion isa promotion;
($book, $promotion) isa promotion-inclusion;
fetch
$book: title;
$promotion: name;

In order to retrieve the correct results, we can use a subquery as follows.

match
$book isa book;
fetch
$book: title;
promotions: {
    match
    $promotion isa promotion;
    ($book, $promotion) isa promotion-inclusion;
    fetch
    $promotion: name;
};

To construct a subquery, we place it inside a block inside the fetch clause, preceded by a subquery label and : delimiter, and followed by a ; delimiter.

match
# parent query match clause
fetch
# parent query fetch clause
subquery-label: {
    # subquery
};

A subquery label can be any string that could be used as a type label. To use a label that is not normally permitted, for instance a reserved keyword or a string containing whitespace, we can enclose it in quotes.

If we run studio run the query above, then we will see it gives the intended results! Let’s examine the subquery to see how this works.

match
$promotion isa promotion;
($book, $promotion) isa promotion-inclusion;
fetch
$promotion: name;

The subquery functions exactly like a regular query, except that variables are shared with the parent query, in this case just $book. The constraints that apply to the parent query also apply to the subquery, but the reverse is not true. That means that the subquery will only return promotions that the associated book is in, but the book does not need to be in a promotion to be matched by the parent query. All subqueries must share at least one variable with the parent query.

For each result of the parent query, the results of the subquery are returned in a list field identified by the subquery label.

Exercise

Write a Fetch query to retrieve a list of books along with any contributors to those books. Ensure the following information is retrieved:

  • Titles of books.

  • Names of contributors.

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

Schema 
define

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

hardback sub book,
    owns stock;

paperback sub book,
    owns stock;

ebook sub book;

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

company sub entity,
    abstract,
    owns name;

publisher sub company,
    plays publishing:publisher;

courier sub company,
    plays delivery:deliverer;

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

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

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

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

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

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

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

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

city sub place;

state sub place;

country sub place;

contribution sub relation,
    relates contributor,
    relates work;

authoring sub contribution,
    relates author as contributor;

editing sub contribution,
    relates editor as contributor;

illustrating sub contribution,
    relates illustrator as contributor;

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

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

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

rating sub relation,
    relates review,
    relates rated;

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

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

locating sub relation,
    relates located,
    relates location;

recommendation sub relation,
    relates recommended,
    relates recipient;

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

rule review-verified-by-purchase:
    when {
        ($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;
    } then {
        $review has verified true;
    };

rule review-unverified:
    when {
        $review isa review;
        not { $review has verified true; };
    } then {
        $review has verified false;
    };

rule book-recommendation-by-genre:
    when {
        $user isa user;
        $liked-book isa book;
        {
            ($user, $order) isa action-execution;
            ($order, $liked-book) isa order-line;
        } or {
            ($user, $review) isa action-execution;
            ($review, $liked-book) isa rating;
            $review has score >= 7;
        };
        $new-book isa book;
        not { {
            ($user, $order) isa action-execution;
            ($order, $new-book) isa order-line;
        } or {
            ($user, $review) isa action-execution;
            ($review, $new-book) isa rating;
        }; };
        $liked-book has genre $shared-genre;
        $new-book has genre $shared-genre;
        not { {
            $shared-genre == "fiction";
        } or {
            $shared-genre == "nonfiction";
        }; };
    } then {
        (recommended: $new-book, recipient: $user) isa recommendation;
    };

rule book-recommendation-by-author:
    when {
        $user isa user;
        $liked-book isa book;
        {
            ($user, $order) isa action-execution;
            ($order, $liked-book) isa order-line;
        } or {
            ($user, $review) isa action-execution;
            ($review, $liked-book) isa rating;
            $review has score >= 7;
        };
        $new-book isa book;
        not { {
            ($user, $order) isa action-execution;
            ($order, $new-book) isa order-line;
        } or {
            ($user, $review) isa action-execution;
            ($review, $new-book) isa rating;
        }; };
        ($liked-book, $shared-author) isa authoring;
        ($new-book, $shared-author) isa authoring;
    } then {
        (recommended: $new-book, recipient: $user) isa recommendation;
    };

rule order-line-total-retail-price:
    when {
        ($order) isa action-execution, has timestamp $order-time;
        $line ($order, $item) isa order-line;
        not {
            ($promotion, $item) isa promotion-inclusion;
            $promotion has start-timestamp <= $order-time,
                has end-timestamp >= $order-time;
        };
        $item has price $retail-price;
        $line has quantity $quantity;
        ?line-total = $quantity * $retail-price;
    } then {
        $line has price ?line-total;
    };

rule order-line-total-discounted-price:
    when {
        ($order) isa action-execution, has timestamp $order-time;
        $line ($order, $item) isa order-line;
        ($best-promotion, $item) isa promotion-inclusion, has discount $best-discount;
        $best-promotion has start-timestamp <= $order-time,
            has end-timestamp >= $order-time;
        not {
            ($other-promotion, $item) isa promotion-inclusion, has discount > $best-discount;
            $other-promotion has start-timestamp <= $order-time,
                has end-timestamp >= $order-time;
        };
        $item has price $retail-price;
        ?discounted-price = round(100 * $retail-price * (1 - $best-discount)) / 100;
        $line has quantity $quantity;
        ?line-total = $quantity * ?discounted-price;
    } then {
        $line has price ?line-total;
    };

rule transitive-location:
    when {
        (location: $parent-place, located: $child-place) isa locating;
        (location: $child-place, located: $x) isa locating;
    } then {
        (location: $parent-place, located: $x) isa locating;
    };
Sample solution 
match
$book isa book;
fetch
$book: title;
contributors: {
    match
    $contributor isa contributor;
    ($book, $contributor) isa contribution;
    fetch
    $contributor: name;
};

Nesting subqueries

In the same way the fetch clause of a query can contain a subquery, the subquery itself can contain further nested subqueries. Let’s see an example. In the following, we list all publishers, any books they have published, and any promotions those books are in.

match
$publisher isa publisher;
fetch
$publisher: name;
books: {
    match
    $book isa book;
    ($book, $publisher) isa publishing;
    fetch
    $book: title;
    promotions: {
        match
        $promotion isa promotion;
        ($book, $promotion) isa promotion-inclusion;
        fetch
        $promotion: name;
    };
};

In effect, we have added a level of grouping to the previous query for books and promotions, now grouping the books by publisher. In general, if we want to introduce a new level of grouping to our query results, we can do so by using the query that retrieved those results as a subquery, and query for the groups in the parent query. There is no limit to the depth that subqueries can be nested, though performance can become a concern for very deep nesting.

Exercise

Write a Fetch query to retrieve a list of users, any orders they have placed, and any books in those orders. Ensure the following information is retrieved:

  • IDs of users.

  • IDs of orders.

  • ISBN-13s of books.

  • The quantities of books ordered.

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

Schema 
define

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

hardback sub book,
    owns stock;

paperback sub book,
    owns stock;

ebook sub book;

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

company sub entity,
    abstract,
    owns name;

publisher sub company,
    plays publishing:publisher;

courier sub company,
    plays delivery:deliverer;

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

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

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

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

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

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

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

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

city sub place;

state sub place;

country sub place;

contribution sub relation,
    relates contributor,
    relates work;

authoring sub contribution,
    relates author as contributor;

editing sub contribution,
    relates editor as contributor;

illustrating sub contribution,
    relates illustrator as contributor;

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

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

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

rating sub relation,
    relates review,
    relates rated;

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

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

locating sub relation,
    relates located,
    relates location;

recommendation sub relation,
    relates recommended,
    relates recipient;

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

rule review-verified-by-purchase:
    when {
        ($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;
    } then {
        $review has verified true;
    };

rule review-unverified:
    when {
        $review isa review;
        not { $review has verified true; };
    } then {
        $review has verified false;
    };

rule book-recommendation-by-genre:
    when {
        $user isa user;
        $liked-book isa book;
        {
            ($user, $order) isa action-execution;
            ($order, $liked-book) isa order-line;
        } or {
            ($user, $review) isa action-execution;
            ($review, $liked-book) isa rating;
            $review has score >= 7;
        };
        $new-book isa book;
        not { {
            ($user, $order) isa action-execution;
            ($order, $new-book) isa order-line;
        } or {
            ($user, $review) isa action-execution;
            ($review, $new-book) isa rating;
        }; };
        $liked-book has genre $shared-genre;
        $new-book has genre $shared-genre;
        not { {
            $shared-genre == "fiction";
        } or {
            $shared-genre == "nonfiction";
        }; };
    } then {
        (recommended: $new-book, recipient: $user) isa recommendation;
    };

rule book-recommendation-by-author:
    when {
        $user isa user;
        $liked-book isa book;
        {
            ($user, $order) isa action-execution;
            ($order, $liked-book) isa order-line;
        } or {
            ($user, $review) isa action-execution;
            ($review, $liked-book) isa rating;
            $review has score >= 7;
        };
        $new-book isa book;
        not { {
            ($user, $order) isa action-execution;
            ($order, $new-book) isa order-line;
        } or {
            ($user, $review) isa action-execution;
            ($review, $new-book) isa rating;
        }; };
        ($liked-book, $shared-author) isa authoring;
        ($new-book, $shared-author) isa authoring;
    } then {
        (recommended: $new-book, recipient: $user) isa recommendation;
    };

rule order-line-total-retail-price:
    when {
        ($order) isa action-execution, has timestamp $order-time;
        $line ($order, $item) isa order-line;
        not {
            ($promotion, $item) isa promotion-inclusion;
            $promotion has start-timestamp <= $order-time,
                has end-timestamp >= $order-time;
        };
        $item has price $retail-price;
        $line has quantity $quantity;
        ?line-total = $quantity * $retail-price;
    } then {
        $line has price ?line-total;
    };

rule order-line-total-discounted-price:
    when {
        ($order) isa action-execution, has timestamp $order-time;
        $line ($order, $item) isa order-line;
        ($best-promotion, $item) isa promotion-inclusion, has discount $best-discount;
        $best-promotion has start-timestamp <= $order-time,
            has end-timestamp >= $order-time;
        not {
            ($other-promotion, $item) isa promotion-inclusion, has discount > $best-discount;
            $other-promotion has start-timestamp <= $order-time,
                has end-timestamp >= $order-time;
        };
        $item has price $retail-price;
        ?discounted-price = round(100 * $retail-price * (1 - $best-discount)) / 100;
        $line has quantity $quantity;
        ?line-total = $quantity * ?discounted-price;
    } then {
        $line has price ?line-total;
    };

rule transitive-location:
    when {
        (location: $parent-place, located: $child-place) isa locating;
        (location: $child-place, located: $x) isa locating;
    } then {
        (location: $parent-place, located: $x) isa locating;
    };
Sample solution 
match
$user isa user, has id $user-id;
fetch
$user-id;
orders: {
    match
    $order isa order, has id $order-id;
    ($user, $order) isa action-execution;
    fetch
    $order-id;
    books: {
        match
        $book isa book, has isbn-13 $isbn;
        ($order, $book) isa order-line,
            has quantity $quantity;
        fetch
        $isbn;
        $quantity;
    };
};

Because none of the attributes we are retrieving should be multivalued, we can safely retrieve them directly rather than by owner, as explained in Lesson 8.1. This is not essential to the solution!

Provide Feedback