Selecting data

The types used in these queries are defined here.

Select a Movie with associated actors and reviews with their authors:

select Movie {
    id,
    title,
    year,
    description,
    actors: {
        id,
        full_name,
    },
    reviews := .<movie[is Review] {
        id,
        body,
        rating,
        author: {
            id,
            name,
        }
    },
}
filter .id = <uuid>'09c34154-4148-11ea-9c68-5375ca908326'

Select movies with Keanu Reeves:

select Movie {
    id,
    title,
    year,
    description,
}
filter .actors.full_name = 'Keanu Reeves'

Select all actors that share the last name with other actors and include the same-last-name actor list as well:

select Person {
    id,
    full_name,
    same_last_name := (
        with
            P := detached Person
        select P {
            id,
            full_name,
        }
        filter
            # same last name
            P.last_name = Person.last_name
            and
            # not the same person
            P != Person
    ),
}
filter exists .same_last_name

The same query can be refactored moving the with block to the top-level:

with
    # don't need detached at top-level
    P := Person
select Person {
    id,
    full_name,
    same_last_name := (
        select P {
            id,
            full_name,
        }
        filter
            # same last name
            P.last_name = Person.last_name
            and
            # not the same person
            P != Person
    ),
}
filter exists .same_last_name

Select user names and the number of reviews they have:

select (
    User.name,
    count(User.<author[is Review])
)

For every user and movie combination, select whether the user has reviewed the movie (beware, in practice this maybe a very large result):

select (
    User.name,
    Movie.title,
    Movie in User.<author[is Review].movie
)

Perform a set intersection of all actors with all directors:

with
    # get the set of actors and set of directors
    Actor := Movie.actors,
    Director := Movie.director,
# set intersection is done via the filter clause
select Actor filter Actor in Director;

To order a set of scalars first assign the set to a variable and use the variable in the order by clause.

select numbers := {3, 1, 2} order by numbers;
# alternatively
with numbers := {3, 1, 2}
select numbers order by numbers;

Selecting free objects.

It is also possible to package data into a free object. Free objects are meant to be transient and used either to more efficiently store some intermediate results in a query or for re-shaping the output. The advantage of using free objects over tuples is that it is easier to package data that potentially contains empty sets as links or properties of the free object. The underlying type of a free object is std::FreeObject.

Consider the following query:

with U := (select User filter .name like '%user%')
select {
    matches := U {name},
    total := count(U),
    total_users := count(User),
};

The matches are potentially {}, yet the query will always return a single free object with results, total, and total_users. To achieve the same using a named tuple, the query would have to be modified like this:

with U := (select User filter .name like '%user%')
select (
    matches := array_agg(U {name}),
    total := count(U),
    total_users := count(User),
);

Without the array_agg() the above query would return {} instead of the named tuple if no matches are found.

︙