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

Lesson 8.1: Fetching attributes

Retrieval syntax

Fetch queries allow us to retrieve attributes, as we learned in Lesson 3.1, and schema types, as we learned in Lesson 3.4. We have also seen queries that retrieve value variables, like this one from Lesson 7.5.

match
$order isa order, has id "o0014";
$book isa book, has price $retail-price;
($order, $book) isa order-line,
    has quantity $quantity;
?line-total = $quantity * $retail-price;
fetch
?line-total;

In previous queries, we have retrieved attributes from the entities or relations that own them, using the following syntax.

fetch
$owner: attribute-type;

But we can also retrieve them directly, in the same way we do value variables.

fetch
$attribute;

Let’s modify the previous query to retrieve the book price along with the line total in each of these two ways. First, by retrieving the price via the book.

match
$order isa order, has id "o0014";
$book isa book, has price $retail-price;
($order, $book) isa order-line,
    has quantity $quantity;
?line-total = $quantity * $retail-price;
fetch
$book: price;
?line-total;

Second, by retrieving the price directly.

match
$order isa order, has id "o0014";
$book isa book, has price $retail-price;
($order, $book) isa order-line,
    has quantity $quantity;
?line-total = $quantity * $retail-price;
fetch
$retail-price;
?line-total;

Try running studio run these queries to see the difference. In addition to having two different output formats, these queries act differently on the data. In this lesson, we’ll examine the difference between these two methods and learn when to apply each one.

Retrieval via the owner

Let’s start with a simple example, in which we retrieve the titles and genres of hardback books.

match
$book isa hardback;
fetch
$book: title, genre;
{
    "book": {
        "genre": [
            { "value": "comics", "value_type": "string", "type": { "label": "genre", "root": "attribute" } },
            { "value": "fiction", "value_type": "string", "type": { "label": "genre", "root": "attribute" } }
        ],
        "title": [ { "value": "The Complete Calvin and Hobbes", "value_type": "string", "type": { "label": "title", "root": "attribute" } } ],
        "type": { "label": "hardback", "root": "entity" }
    }
}
{
    "book": {
        "genre": [
            { "value": "history", "value_type": "string", "type": { "label": "genre", "root": "attribute" } },
            { "value": "nonfiction", "value_type": "string", "type": { "label": "genre", "root": "attribute" } }
        ],
        "title": [ { "value": "Under the Black Flag: The Romance and the Reality of Life Among the Pirates", "value_type": "string", "type": { "label": "title", "root": "attribute" } } ],
        "type": { "label": "hardback", "root": "entity" }
    }
}
{
    "book": {
        "genre": [
            { "value": "nonfiction", "value_type": "string", "type": { "label": "genre", "root": "attribute" } },
            { "value": "technology", "value_type": "string", "type": { "label": "genre", "root": "attribute" } }
        ],
        "title": [ { "value": "Electron Backscatter Diffraction in Materials Science", "value_type": "string", "type": { "label": "title", "root": "attribute" } } ],
        "type": { "label": "hardback", "root": "entity" }
    }
}

Each result contains one JSON object, in the book field. Each of these objects represents a data instance matched by $book. Within this object, we have the type of the data instance in the type field, and a field for each attribute type in its projection, namely title and genre. The projected attribute fields are lists, which contain one item for each attribute of that type owned by the $book data instance.

We can see that each book has one title and two genres, but these lists could both contain any number of titles and genres if more existed in the data. Similarly, if a book did not own any instances of a retrieved attribute type, we would get back an empty list. As a result, fetching attributes via their owners matches the attributes optionally. If a particular attribute type in the fetch clause is absent in the data, it will not prevent the owner from being matched and returned.

Direct retrieval

Now let’s try retrieving the attributes directly. To do so, we’ll need to modify the match clause to declare variables for them.

match
$book isa hardback,
    has title $title,
    has genre $genre;
fetch
$title;
$genre;
{
    "genre": { "value": "history", "value_type": "string", "type": { "label": "genre", "root": "attribute" } },
    "title": { "value": "Under the Black Flag: The Romance and the Reality of Life Among the Pirates", "value_type": "string", "type": { "label": "title", "root": "attribute" } }
}
{
    "genre": { "value": "nonfiction", "value_type": "string", "type": { "label": "genre", "root": "attribute" } },
    "title": { "value": "Under the Black Flag: The Romance and the Reality of Life Among the Pirates", "value_type": "string", "type": { "label": "title", "root": "attribute" } }
}
{
    "genre": { "value": "nonfiction", "value_type": "string", "type": { "label": "genre", "root": "attribute" } },
    "title": { "value": "Electron Backscatter Diffraction in Materials Science", "value_type": "string", "type": { "label": "title", "root": "attribute" } }
}
{
    "genre": { "value": "technology", "value_type": "string", "type": { "label": "genre", "root": "attribute" } },
    "title": { "value": "Electron Backscatter Diffraction in Materials Science", "value_type": "string", "type": { "label": "title", "root": "attribute" } }
}
{
    "genre": { "value": "comics", "value_type": "string", "type": { "label": "genre", "root": "attribute" } },
    "title": { "value": "The Complete Calvin and Hobbes", "value_type": "string", "type": { "label": "title", "root": "attribute" } }
}
{
    "genre": { "value": "fiction", "value_type": "string", "type": { "label": "genre", "root": "attribute" } },
    "title": { "value": "The Complete Calvin and Hobbes", "value_type": "string", "type": { "label": "title", "root": "attribute" } }
}

This output format is significantly different to the previous one. Each result now contains two JSON objects. This is to be expected, as the results of Fetch queries contain one object per variable in the fetch clause, and we now have two variables there. We are also no longer retrieving the type of $book as it is no longer present in the fetch clause. Other than this one difference, the information we are getting back is the same, but organised in an alternate manner.

The biggest difference is that there are now six results returned instead of three. Examining them, we can see that each book’s title is returned once with each of the two book’s genres. When retrieving attributes directly, they are not grouped by owner, so exactly one result is returned per unique combination of attributes. This also means that these attributes are no longer optional. Because they are now part of the match clause, ownership of them is a constraint that must be satisfied, as we explored in Lesson 7.1.

Comparing methods

These two methods of attribute retrieval have different properties, which makes them suited to different queries. Retrieving attributes via the owner:

  • Returns attributes grouped into results by owner.

  • Matches attributes optionally.

Meanwhile, retrieving attributes directly:

  • Returns one attribute per result.

  • Matches attributes as a required constraint.

For most use cases, it is preferable to retrieve attributes via the owner. In some cases, it might be preferable to retrieve them directly, for instance if we specifically want to retrieve attributes in ungrouped combinations, or if we can be sure that each owner will own exactly one attribute (if leveraging a key constraint, for example).

Exercise

Write a Fetch query to retrieve the ISBNs of all books in the database (both ISBN-13s and ISBN-10s), grouped by book.

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: isbn;

Write another Fetch query to retrieve the ISBNs of all books in the database, without grouping by book.

Sample solution 
match
$book isa book, has isbn $isbn;
fetch
$isbn;

Provide Feedback