Relations

Filtering across Lucid relationships — dotted paths that become whereHas subqueries, filtering the rows you preload, and sorting by a relation aggregate.

This library does not invent join machinery. A relation filter becomes Lucid's own whereHas, a filtered eager-load becomes Lucid's own preload callback, and a relation aggregate is Lucid's own withCount. All of it works because a relation sub-query is itself a QueryBuilderLike — the same contract the root builder satisfies.

Two things show up in a filtered endpoint, and they are not the same question: which parents come back (a whereHas constraint), and which related rows you load for them (a preload constraint).

A dotted field is a relation path

Declare the hop in the spec's relations map and the dotted path becomes filterable. Every segment but the last is a relation; the last is the column the operator lands on inside the subquery:

app/filters/user_filter.ts

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

export const userFilter = defineFilter({
  model: User,

  filterable: ['name', 'email', 'status'],
  sortable: ['name', 'createdAt'],

  relations: {
    posts: { filterable: ['title', 'status'] }, // enables filter[posts.title] / filter[posts.status]
  },
})

GET /users?filter[posts.status]=published
→ User.query().whereHas('posts', (q) => q.where('status', 'published'))

The relation does not have to be joined — Lucid builds the correlated subquery. Deeper paths nest: posts.comments.body becomes a whereHas inside a whereHas, capped by the spec's maxDepth. The declarative form and its depth rules are covered in Relation Filtering.

A dotted filter is a subquery, not a joined column

If you have genuinely joined the relation and want a predicate on the joined table (where('posts.title', …)) rather than an EXISTS subquery, select an alias for that column and filter on the alias — or drop to applyColumnFilters on the joined builder.

Filtering the rows you preload

preload's callback receives a relation query builder, so a second filter can run inside it. This narrows the loaded posts without changing which users come back:

app/controllers/users_controller.ts

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

export default class UsersController {
  async index(ctx: HttpContext) {
    // parents read `filter[...]`; the preloaded relation reads its own param
    const postInput = parseFilterRequest({ filter: ctx.request.qs().postFilter })

    const query = User.query().preload('posts', (postsQuery) => {
      applyFilter(postsQuery, postInput, {
        allowed: ['title', 'status'],
        sortable: ['createdAt'],
      })
    })

    const { page, size } = applyFilterFromRequest(query, userFilter, ctx)

    return query.paginate(page, size)
  }
}

Each call carries its own allow-list, so what a client may filter on the relation is scoped independently of the parent.

Ignore the { page, size } returned inside a preload callback. Paginating a to-many relation per parent row corrupts the counts; if the return value tempts you, call the lower-level applyColumnFilters / applySort there instead.

Constraining the parent yourself

When the constraint is a server policy rather than a client filter, write the whereHas directly — the low-level stage functions apply one piece of the pipeline to any sub-builder:

app/controllers/users_controller.ts

import { applyColumnFilters } from '@adonis-agora/filter'

const query = User.query().whereHas('posts', (postsQuery) => {
  applyColumnFilters(postsQuery, [
    { field: 'status', operator: 'equals', value: 'published' },
    { field: 'title', operator: 'contains', value: 'release' },
  ])
})
// users with at least one post where status = 'published' and title ilike '%release%'

The stage functions trust their input

applyColumnFilters, applySort and applySearch do not allow-list. Hand them filters you wrote, or prune client input first — otherwise a query string reaches columns the spec never exposed.

Sorting by a relation aggregate

withCount selects a posts_count column, and a column that exists can be sorted on. List the alias in sortable and ?sort=-posts_count works like any other sort:

app/controllers/users_controller.ts

export default class UsersController {
  async index(ctx: HttpContext) {
    const query = User.query().withCount('posts')

    const { page, size } = applyFilterFromRequest(query, userFilter, ctx)

    return query.paginate(page, size)
  }
}

app/filters/user_filter.ts

export const userFilter = defineFilter({
  model: User,
  filterable: ['name', 'status'],
  sortable: ['name', 'createdAt', 'posts_count'], // the alias withCount selects
})

For a count that the spec derives on its own — including filtering on it, not just sorting — use a computed aggregate instead of withCount.

A dotted field is a relation path

Filtering the rows you preload

Constraining the parent yourself

Sorting by a relation aggregate

On this page