Aggregates

The syntax for aggregate functions is slightly different than basic matching.

GQL supports using aggregate functions (e.g. count, sum, max, min or avg) to filter results. It's relevant to note that this is different from returning the value of the aggregate function in the returned result set. The underlying query semantics are different.

Using aggregates with GQL involves two parts:

First, use the $having GQL property identifier. $having identifies a property which should be applied with a HAVING SQL clause rather than a WHERE. This makes it relevant to all aggregate functions.

Prepend $having. (notice the period) in front of a property name to indicate that it should be part of a having clause and not a where. The name of the property will often be an alias (e.g. count(posts.id) AS post_count).

Then we actually calculate the aggregate using the aggregate functions built into knex (again, sum, count, max, min, avg).

For example:

// Join the users and posts tables
var usersAndPosts = knex('users')
    .join('posts', 'users.id', 'posts.author_id')
    .groupBy('users.id');

gql.parse('$having.post_count:>0')
    .applyTo(usersAndPosts) //
    .select('users.*') // specify which fields to get
    .count('posts.id as post_count')

The generated SQL for the query above looks like this:

SELECT users.*, COUNT(posts.id) AS post_count
    FROM users
    INNER JOIN posts ON posts.author_id = users.id
    GROUP BY users.id
    HAVING post_count > 0;