Lucid Integration

Every filter in this library ends the same way: as clauses added to a Lucid query builder you created. Not a wrapper around one, not a repository object that hides it — the same User.query() you would have written by hand, with a few more wheres on it.

Step through what a single request does to it:

1export default class UsersController {2  async index(ctx: HttpContext) {3    const { query } = await User.filter(ctx)4 5    query.preload('team')6 7    return query.filterPaginate()8  }9}

The builder stays yours

applyFilterFromRequest (and its lower-level sibling applyFilter) mutate the builder and return the pagination. They never construct a query, never execute one, and never hand you back a different object:

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)
  }
}

That one property is what makes the rest of Lucid keep working. Anything you do to the builder before the call is still there afterwards, and anything you do after composes with what the filter added:

async index(ctx: HttpContext) {
  const query = User.query()
    .where('tenantId', ctx.auth.user!.tenantId) // forced — the client cannot relax it
    .whereNull('deletedAt')
    .preload('team')
    .withCount('posts')

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

  return query.paginate(page, size)
}

Ordering matters in exactly one place: your own orderBy runs before the request's, so a client sort becomes a tiebreaker rather than a replacement. If the client's ordering should win, add yours after the call, or express it as the spec's defaultSort — which only applies when the request carries no sort of its own.

Pagination is resolved, not run

The return value is { page, size } — already clamped, not yet executed. Handing it to Lucid's paginator is your line of code:

const { page, size } = applyFilterFromRequest(query, userFilter, ctx)
const users = await query.paginate(page, size)

size is clamped to [1, maxSize] (cap 100, default defaultSize ?? 25) and page has a floor of 1, so ?size=100000&page=-4 is a normal request rather than a table scan. A client can shrink a page but never enlarge it past the cap you declared.

Not every list wants a paginator. Ignore the return value and execute the query directly — the WHERE and ORDER BY clauses are already on it:

async index(ctx: HttpContext) {
  const query = User.query()
  applyFilterFromRequest(query, userFilter, ctx)

  return query.limit(5_000).exec() // filtered, sorted, un-paginated
}

For keyset pagination over the same spec — stable pages over a moving table, and the right choice for infinite scroll — use applyCursorFromRequest instead.

Facet endpoints, with distinct

Because the builder comes back filtered but unexecuted, you can project something other than rows off it. The classic use is populating a filter dropdown with the values that still exist under the filters already applied:

async statuses(ctx: HttpContext) {
  const query = User.query()
  applyFilterFromRequest(query, userFilter, ctx)

  // "Which statuses exist among the rows matching the current filter?"
  const rows = await query.distinct('status').select('status')

  return rows.map((row) => row.status)
}

You rarely need to write that by hand, though: distinct is part of the wire format. Both parseFilterRequest and parseSpatieRequest read ?distinct=status,baseId (and the repeated distinct[]=status&distinct[]=baseId form) into the parsed input, and the runner applies it for you — alias-resolved and allow-listed exactly like a where field. The client builder's .distinct(...) emits that parameter, so the round trip needs no code on your side:

// GET /users?filter[status]=active&distinct=baseId
async index(ctx: HttpContext) {
  const query = User.query()
  applyFilterFromRequest(query, userFilter, ctx) // applies the DISTINCT too

  return query.select('baseId')
}

The manual query.distinct(...) above is still the right tool when the projection is your decision rather than the client's.

distinct takes root-table columns only

A distinct field must be a column the query's FROM actually holds — a column of the root table, optionally qualified with the root table's own name.

A relation path is refused, even though the same path is perfectly valid to filter on. Relation filtering compiles to a correlated EXISTS subquery, so the relation is never joined into the outer query and there is no alias to project a column from — distinct=posts.title would emit select distinct "posts"."title" from "users", which Postgres rejects with missing FROM-clause entry for table "posts". To-many aggregate paths (posts.$count) are refused for the same reason.

A refused field is dropped — the rest of the distinct list still applies — or raises InvalidColumnFilterError when the spec sets throwOnInvalid. Adding the path to filterable/relations will not change this; it is not an allow-list decision.

Queries that are not models

Everything above works on a plain database query too — a reporting view, a table without a model, a CTE you built with the query builder:

import type { HttpContext } from '@adonisjs/core/http'
import { applyFilterFromRequest } from '@adonis-agora/filter'
import { signupReportFilter } from '#filters/signup_report_filter'
import db from '@adonisjs/lucid/services/db'

export default class ReportsController {
  async signups(ctx: HttpContext) {
    const query = db.from('signup_report').where('archived', false)

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

    return query.paginate(page, size)
  }
}

The one thing a model gives you that a raw table does not is relation metadata: a spec's model option is what unlocks to-many aggregates like posts.$count. Everything else — operators, search, sort, distinct, cursors — is identical.

Applying one stage to a sub-query

Inside a preload or whereHas callback you usually do not want the whole pipeline: no allow-listing (you already decided what goes in there), no pagination (paginating a to-many relation per parent row corrupts its counts). Three functions apply a single stage to any builder:

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

const query = User.query().preload('posts', (posts) => {
  applyColumnFilters(posts, [
    { field: 'status', operator: 'equals', value: 'published' },
  ])
  applySort(posts, [{ field: 'createdAt', direction: 'desc' }])
})

applySearch(qb, term, columns) is the third — it OR-combines an ILIKE across the columns you name, grouped, so it narrows the surrounding query instead of widening it.

Relations covers the surrounding patterns: filtering a preloaded relation from its own request parameter, constraining parents with whereHas, and sorting by a withCount alias.

Anything with these methods is a query builder

The library has no runtime dependency on Lucid. It targets a small structural interface, QueryBuilderLike, which every Lucid builder — model query, database query, relation sub-query — already satisfies, so you can pass one straight in with no cast and no adapter.

You only need to read this section if you are implementing the interface yourself: a test double, or a builder for something that is not Lucid at all. Every member is required.

interface QueryBuilderLike {
  where(callback: (qb: QueryBuilderLike) => void): QueryBuilderLike;
  where(column: string, value: unknown): QueryBuilderLike;
  where(column: string, operator: string, value: unknown): QueryBuilderLike;
  orWhere(callback: (qb: QueryBuilderLike) => void): QueryBuilderLike;
  // biome-ignore lint: `relation` is `any` so real Lucid builders satisfy this.
  whereHas(relation: any, callback: (qb: QueryBuilderLike) => void): QueryBuilderLike;
  whereNot(column: string, value: unknown): QueryBuilderLike;
  whereIn(column: string, values: unknown[]): QueryBuilderLike;
  whereNotIn(column: string, values: unknown[]): QueryBuilderLike;
  whereNull(column: string): QueryBuilderLike;
  whereNotNull(column: string): QueryBuilderLike;
  whereBetween(column: string, range: [unknown, unknown]): QueryBuilderLike;
  whereNotBetween(column: string, range: [unknown, unknown]): QueryBuilderLike;
  whereILike(column: string, value: string): QueryBuilderLike;
  orWhereILike(column: string, value: string): QueryBuilderLike;
  whereRaw(sql: string, bindings?: readonly unknown[]): QueryBuilderLike;
  orderBy(column: string, direction: 'asc' | 'desc'): QueryBuilderLike;
  orderByRaw(sql: string, bindings?: readonly unknown[]): QueryBuilderLike;
  distinct(...columns: string[]): QueryBuilderLike;
  limit(count: number): QueryBuilderLike;
}

The nested-callback where/orWhere overloads are how AND/OR composition is rendered — they model Lucid's grouping closures. The last five members carry everything that cannot be expressed as a plain column comparison, and they are the ones a hand-written stand-in forgets:

You do not have to write that class for tests: @adonis-agora/filter/testing ships a recording MockQueryBuilder that already implements it and records every call for assertion. See Testing Utilities.