Relational Commands
The list of records passed through selection sets in Gelly can be changed using relational commands like where
, limit
, order by
and group by
. Relational commands are always enclosed in square brackets within a selection set and only affect the returned records for that level of the selection.
For example, we can limit the list of records returned with the [limit 5]
relational command:
gelly1query {2 posts {3 id4 title5 [limit 5]6 }7}
Relational commands change the data returned by the query, but don't change the data stored in the database. Relational commands are able to use expressions that use data from the model being queried. For example, if the Blog Post model had a published
boolean field on it, then we can use the where
relational command to filter the list of returned posts down to only those that are published.
gelly1query {2 posts {3 id4 title5 [where published]6 }7}
We can also use expressions to filter returned records. For example, we could return only posts with a score above a certain threshold that are also published:
gelly1query {2 posts {3 id4 title5 [where published && score > 10]6 }7}
Relational commands can be used within inner, nested selection sets without affecting the outer selection set. For example, we can select the top three comments for each post sorted by comment score:
gelly1query {2 posts {3 id4 title5 comments {6 id7 body8 [order by score desc limit 3]9 }10 }11}
We can also issue multiple relational commands to inner and outer selection sets simultaneously. For example, we can select the top three comments for each post, and only select posts-with-comments for posts that are published, and only return the most recent 3 posts:
gelly1query {2 posts {3 id4 title5 [where published limit 3 order by createdDate desc]67 comments {8 id9 body10 [order by score desc limit 3]11 }12 }13}
The order of relational commands within the [...]
stanza doesn't matter, and the position of the command list among all the fields in a selection set also doesn't matter.
Relational commands are very similar (and in fact powered by) the SQL query clauses of the same name, and tend to work the same way as their SQL counterparts. If you are familiar with SQL, you can use the relational commands like you would use the same keywords in SQL!
The limit
command
The limit
relational command ensures a maximum number of results are returned for the selection set. A selection set containing [limit 10]
will return a maximum of 10 records. The limit
command must be passed one fixed integer, and can't use dynamic expressions to choose how many rows are returned. If the underlying model has fewer records than the limit
, or if a where
command filters them out, up to the limit records will be returned.
[limit <number>]
For example, we could select up to of 5 posts with the limit
command.
1query {2 posts {3 title4 [limit 5]5 }6}
The limit
command can be used more than once, on both an outer selection and an inner selection. For example, we could select 5 posts and 3 comments for each post with two limit
commands.
1query {2 posts {3 title4 [limit 5]5 comments {6 body7 [limit 3]8 }9 }10}
The limit
command is often combined with the order by
command in order to get the top records by some criteria. For example, we can get the top 10 scoring posts ever by combining the two commands.
1query {2 posts {3 title4 score5 [order by score desc limit 10]6 }7}
The order by
command
The order by
relational command sorts the returned records by the specified expression
in either ascending or descending order. The default sort direction is ascending, where the record with the lowest value will be first, and the record with the highest value will be last. Most, but not all data types that can be ordered by.
[order by <expression> <asc|desc>]
For example, order by
can ensure the most recent records for a table are returned by using [order by createdDate desc]
.
1query {2 posts {3 title4 [order by createdDate desc]5 }6}
The expression
can a rich computation on any available data instead of just a simple field reference.
1query {2 posts {3 title4 [order by author.scoreWeight * post.score]5 }6}
The where
command
The where
command filters the stored records down to only return some of them. where
expects to be passed a boolean expression and will include records where the expression evaluates to true
.
[where <expression>]
For example, you can find only the posts that have a score greater than 0 with the where
command within the posts
selection set:
1query {2 posts {3 title4 [where score > 0]5 }6}
where
command expressions can be arbitrarily complicated. For example, you can query for posts that have a score over 50 by a trusted author, or posts which have a score over 50 by a spammy author.
1query {2 posts {3 title4 score5 author.isSpam6 [where (!author.isSpam && score > 50) || (author.isSpam && score > 100)]7 }8}
Like all relational commands, the where
command can be combined with other relational commands. For example, we can select the top 10 highest scoring posts where the author isn't spammy by using where
, limit
, and order by
.
1query {2 posts {3 title4 score5 [where !author.isSpam order by score desc limit 10]6 }7}
The group by
command
The group by
command allows aggregating individual, smaller buckets of the total records available to return one aggregate result per group. group by
can be used to answer questions like "the top score by city" or "the count of comments made each day for the last 30 days". [group by]
in Gelly is very similar to GROUP BY
in SQL.
[group by <expression> <, ...expression> <, ...expression>]
group by
expects to given the expressions to group the incoming records using. The expression (or expressions) are evaluated for each row in the set being grouped, and then a group is formed for each unique value (or combination of values) for the expressions. Any aggregate functions in the selection set are then evaluated independently for each group instead of across the whole incoming set of records. For example, we could group post records by their author id to count the number of posts each author has made. This query would return one row per unique authorId
value holding the authorId
and the count(id)
counting the number of posts. Be sure to explicitly select the authorId
field in order to know which count is for which authorId
in the results.
1query {2 posts {3 count(id)4 authorId5 [group by authorId]6 }7}
group by
in Gelly tends to be used less than in SQL. There's nothing wrong with using it, but in Gelly, there's often a simpler way of
executing the same query without using group by that most consider clearer, specifically when grouping the related records of a parent
record. If you're grouping a set of records by the same key of a relationship, you can use a subselection, or an inline function call
instead!
The same query above using group by authorId
can instead be written to use an inline aggregation function. This switches the response to be oriented around the authors
model, which allows for getting things like the author's name
field easily, instead of just the counts.
1query {2 authors {3 id4 name5 count(posts.id)6 }7}
group by
can group by arbitrary expressions on fields as well. We could count the number of posts by a score bucket, where we use the modulus operator to round each post score to a multiple of 10. Note that we want to select the scoreBucket
expression so we can associate which count is for which bucket in the results.
1query {2 posts {3 count(id)4 scoreBucket: score % 105 [group by scoreBucket]6 }7}
Like all relational commands, group by
can be combined with other related commands. limit
and order by
affect the group results, and queries are able to order by fields produced by the grouping. For example, we could order the post-counts-by-author results by the resulting post count.
1query {2 posts {3 postCount: count(id)4 authorId5 [group by authorId order by postCount desc]6 }7}
group by
can be combined with where
commands as well. Gelly groups records after they have been filtered by a where
command. This allows you to group only records you are interested in. For example, we can compute the count of posts by author where the post was not marked as a spam.
1query {2 posts {3 count(id)4 authorId5 [6 group by authorId7 where !isSpam8 ]9 }10}
All selected expressions must aggregate the input rows when grouped
Like GROUP BY
in SQL, the group by
command will error if you try to select fields that aren't part of the group expressions. This is because the output rows from a group by
must be aggregated, and Gelly doesn't know what to do with fields that you don't specify an aggregate function for. For example, if we group posts by authorId, and then try to select the count of posts, and each post's score, it is unclear what to do with the score of each post to form the group. To fix this issue, use an aggregate function on each field you want to select which defines how to process the field for the group, or switch the orientation of your query to select from an outer relation where you can select anything you want, and then group a different inner relation.
1query {2 posts {3 count(id)4 # will cause an error because the field is not being aggregated5 score6 [group by authorId]7 }8}