To-many Aggregates

Sometimes the thing you want to filter or sort by lives across a to-many relation and doesn't exist as a column at all: users with more than 5 posts, articles ordered by total views, authors whose most-recent comment score exceeds 10. To-many aggregates expose exactly those — a dotted, self-describing grammar (posts.$count, posts.$sum.views) that becomes a filterable and sortable virtual field.

Under the hood each aggregate is a computed field whose SQL is a correlated subquery, auto-generated from your Lucid model's relation metadata. You don't write the subquery — you declare the relation and (for the column functions) which child columns are numeric, and the library synthesises the rest.

The grammar

An aggregate field is a single relation hop, a $-prefixed function, and — for the column functions — a child column:

So posts.$count, posts.$sum.views, posts.$max.published_at are all valid field names a client can filter or sort on — subject to the whitelist below.

Unlocking aggregates — the model option

Aggregates are off by default. They activate only when the spec can introspect your relations, which needs two things: the owning Lucid model, and a relations whitelist that names the to-many relations.

import { defineFilter } from '@adonis-agora/filter'
import User from '#models/user'

export const userFilter = defineFilter({
  filterable: ['status'],
  sortable: ['createdAt'],

  // (1) The owning model — its relation metadata (FK / pivot columns) is read at
  //     build time to synthesise the aggregate subqueries. Also supplies the
  //     `table` name used as the correlated-subquery outer alias.
  model: User,

  // (2) Whitelist the to-many relations. `$count` is synthesised for every
  //     to-many relation here; the column functions need `aggregates` (below).
  relations: {
    posts: {
      filterable: ['title', 'status'],
      // (3) Assert which child columns are numeric — this is the allow-list for
      //     $sum / $avg / $min / $max on the relation.
      aggregates: ['views', 'likes'],
    },
  },
})

That single spec makes these fields filterable and sortable:

• posts.$count — synthesised because posts is a whitelisted to-many relation.
• posts.$sum.views, posts.$avg.views, posts.$min.views, posts.$max.views
• posts.$sum.likes, posts.$avg.likes, posts.$min.likes, posts.$max.likes

What gets synthesised

defineFilter calls discoverAggregateSources(model, relations) at build time and merges the result into the spec's computed map. The synthesis is relation-kind aware:

-- posts.$count
(SELECT COUNT(*) FROM "posts" WHERE "posts"."author_id" = "users"."id")

-- posts.$sum.views
(SELECT COALESCE(SUM("posts"."views"),0)
   FROM "posts" WHERE "posts"."author_id" = "users"."id")

-- roles.$count
(SELECT COUNT(*) FROM "role_user" WHERE "role_user"."user_id" = "users"."id")

-- roles.$max.level
(SELECT MAX("roles"."level")
   FROM "roles"
   JOIN "role_user" ON "roles"."id" = "role_user"."role_id"
   WHERE "role_user"."user_id" = "users"."id")

The empty-collection semantics are chosen to be least-surprising: $sum coalesces to 0, $count is naturally 0, and $avg/$min/$max stay NULL (their natural result over zero rows). To-one relations (belongsTo / hasOne) are excluded — aggregates are a to-many concept.

Full example — filter and sort by an aggregate

Find active users with at least 5 posts, ordered by their total post views, most first:

import { defineFilter } from '@adonis-agora/filter'
import User from '#models/user'

export const userFilter = defineFilter({
  filterable: ['status'],
  model: User,
  relations: {
    posts: { filterable: ['status'], aggregates: ['views'] },
  },
})

import type { HttpContext } from '@adonisjs/core/http'
import { applyFilterFromRequest } from '@adonis-agora/filter'
import { userFilter } from '#filters/user_filter'
import User from '#models/user'

export default class UsersController {
  async index(ctx: HttpContext) {
    const query = User.query()
    const { page, size } = applyFilterFromRequest(query, userFilter, ctx)
    return query.paginate(page, size)
  }
}

import { filterQuery } from '@adonis-agora/filter-client'

const qs = filterQuery()
  .equals('status', 'active')
  .gte('posts.$count', 5)      // to-many count as a filter
  .sortDesc('posts.$sum.views') // to-many sum as a sort key
  .page(0, 25)
  .toQueryString()

await fetch(`/users?${qs}`)

The request:

GET /users?filter[status]=active&filter[posts.$count][gte]=5&sort=-posts.$sum.views

produces (Postgres):

WHERE "status" = ?                                                          -- 'active'
  AND ((SELECT COUNT(*) FROM "posts" WHERE "posts"."author_id" = "users"."id")) >= ?  -- 5
ORDER BY ((SELECT COALESCE(SUM("posts"."views"),0)
             FROM "posts" WHERE "posts"."author_id" = "users"."id")) DESC

The client value (5) is a bound parameter; the whole subquery is dev-generated. Aggregate fields inherit every property of computed fields — injection safety, operator coverage, and codegen surfacing (so make:filter-client emits posts.$count and posts.$sum.views into the generated field union).