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:

gelly
Copy
1query {
2  posts {
3    id
4    title
5    [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.

gelly
Copy
1query {
2  posts {
3    id
4    title
5    [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:

gelly
Copy
1query {
2  posts {
3    id
4    title
5    [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:

gelly
Copy
1query {
2  posts {
3    id
4    title
5    comments {
6      id
7      body
8      [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:

gelly
Copy
1query {
2  posts {
3    id
4    title
5    [where published limit 3 order by createdDate desc]
6
7    comments {
8      id
9      body
10      [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.

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    title
4    [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    title
4    [limit 5]
5    comments {
6      body
7      [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    title
4    score
5    [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    title
4    [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    title
4    [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    title
4    [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    title
4    score
5    author.isSpam
6    [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    title
4    score
5    [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    authorId
5    [group by authorId]
6  }
7}

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    id
4    name
5    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 % 10
5    [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    authorId
5    [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    authorId
5    [
6      group by authorId
7      where !isSpam
8    ]
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 aggregated
5    score
6    [group by authorId]
7  }
8}