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_nameThe 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_nameSelect 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.