NormQueryBuilder class

The fluent builder behind every query: where_*, joins, aggregations, scopes, pagination, and bulk writes.

Auto-generated

This page is generated from the source annotations by scripts/gen-api.mjs. Edit the LuaCATS doc comments in the Norm sources, not here.

🔗 Source: src/query.lua

Member	Returns	Description
`all`	`NormRecordListPromise` (async)	Execute the query and resolve with all matching records.
`avg`	`NormNumberPromise` (async)	AVG of a column over the current filter.
`count`	`NormNumberPromise` (async)	Resolve with the COUNT(*) for the current conditions.
`decrement`	`NormNumberPromise` (async)	Atomically subtract `amount` (default 1) from a column on every matching row.
`delete`	`NormNumberPromise` (async)	Bulk-delete every matching row.
`first`	`NormRecordOrNilPromise` (async)	Execute the query with LIMIT 1 and resolve with the first record (or nil).
`force_delete`	`NormNumberPromise` (async)	Bulk physical-DELETE every matching row, even on a soft-delete model.
`group_by`	`self: NormQueryBuilder`	Add GROUP BY columns (call again, or pass several, to group by more).
`having`	`self: NormQueryBuilder`	Add a HAVING condition (ANDed) on a RAW aggregate expression.
`include`	`self: NormQueryBuilder`	Eager-load relations with the result (one batched query per relation level —
`increment`	`NormNumberPromise` (async)	Atomically add `amount` (default 1) to a column on every matching row, in one
`join`	`self: NormQueryBuilder`	INNER JOIN another table.
`left_join`	`self: NormQueryBuilder`	LEFT JOIN another table (same argument forms as `:join`).
`limit`	`self: NormQueryBuilder`	Limit the number of rows (pair with `:offset()` for pagination).
`max`	`NormNumberPromise` (async)	MAX of a column over the current filter.
`min`	`NormNumberPromise` (async)	MIN of a column over the current filter.
`model`	—
`offset`	`self: NormQueryBuilder`	Skip `n` rows (use with `:limit()`).
`omit`	`self: NormQueryBuilder`	Inverse of `select`: select every column of the model EXCEPT the given ones
`only_trashed`	`self: NormQueryBuilder`	Return ONLY soft-deleted rows.
`or_where`	`self: NormQueryBuilder`	OR variant of `where`.
`or_where_between`	`self: NormQueryBuilder`
`or_where_in`	`self: NormQueryBuilder`
`or_where_like`	`self: NormQueryBuilder`
`or_where_not`	`self: NormQueryBuilder`
`or_where_not_between`	`self: NormQueryBuilder`
`or_where_not_in`	`self: NormQueryBuilder`
`or_where_not_like`	`self: NormQueryBuilder`
`or_where_not_null`	`self: NormQueryBuilder`
`or_where_null`	`self: NormQueryBuilder`
`order`	`self: NormQueryBuilder`	Add an ORDER BY clause (call again for secondary orderings).
`paginate`	`NormPromise` (async)	Paginate the current query.
`rows`	`NormRowsPromise` (async)	Execute the query and resolve with the RAW rows (no record wrapping).
`scope`	`self: NormQueryBuilder`	Apply a named scope (a reusable query fragment registered on the model with
`select`	`self: NormQueryBuilder`	Restrict selected columns (the inverse is `:omit`).
`select_raw`	`self: NormQueryBuilder`	Add a RAW (unquoted) select expression — for aggregates/computed columns that
`sum`	`NormNumberPromise` (async)	SUM of a column over the current filter.
`update`	`NormNumberPromise` (async)	Bulk-update every matching row in one statement (no records loaded).
`where`	`self: NormQueryBuilder`	Add an AND condition.
`where_between`	`self: NormQueryBuilder`	`column [NOT] BETWEEN min AND max` (inclusive).
`where_doesnt_have`	`self: NormQueryBuilder`	Inverse of `where_has`: keep only rows with NO matching related row
`where_has`	`self: NormQueryBuilder`	Keep only rows that HAVE at least one related row for `name` (optionally
`where_in`	`self: NormQueryBuilder`	`column IN (...)` (and its OR / negated variants).
`where_like`	`self: NormQueryBuilder`	`column [NOT] LIKE pattern` (use `%` / `_` wildcards).
`where_not`	`self: NormQueryBuilder`	`column != value` (and OR variant).
`where_not_between`	`self: NormQueryBuilder`
`where_not_in`	`self: NormQueryBuilder`
`where_not_like`	`self: NormQueryBuilder`
`where_not_null`	`self: NormQueryBuilder`
`where_null`	`self: NormQueryBuilder`	`column IS [NOT] NULL` (and OR variants).
`with_count`	`self: NormQueryBuilder`	Add a `<name>_count` field to each returned record: the number of related rows,
`with_trashed`	`self: NormQueryBuilder`	Include soft-deleted rows in the result (disables the default exclusion).

all async method

lua

NormQueryBuilder:all()
  -> promise: NormRecordListPromise

Execute the query and resolve with all matching records.

lua

    local users = User:query():where("admin", true):all():await()

avg async method

lua

NormQueryBuilder:avg(column: string)
  -> promise: NormNumberPromise

AVG of a column over the current filter. Resolves with a number.

count async method

lua

NormQueryBuilder:count()
  -> promise: NormNumberPromise

Resolve with the COUNT(*) for the current conditions.

lua

    local admins = User:query():where("admin", true):count():await()

decrement async method

lua

NormQueryBuilder:decrement(column: string, amount?: number)
  -> promise: NormNumberPromise

Atomically subtract amount (default 1) from a column on every matching row.

delete async method

lua

NormQueryBuilder:delete()
  -> promise: NormNumberPromise

Bulk-delete every matching row. On a soft-delete model this marks the rows (sets deleted_at) rather than removing them; use force_delete to remove. Resolves with the affected row count.

lua

    local n = User:query():where("coins", 0):delete():await()

first async method

lua

NormQueryBuilder:first()
  -> promise: NormRecordOrNilPromise

Execute the query with LIMIT 1 and resolve with the first record (or nil).

lua

    local newest = User:query():order("id", "DESC"):first():await()

force_delete async method

lua

NormQueryBuilder:force_delete()
  -> promise: NormNumberPromise

Bulk physical-DELETE every matching row, even on a soft-delete model. Resolves with the affected row count.

group_by method

lua

NormQueryBuilder:group_by(...string)
  -> self: NormQueryBuilder

Add GROUP BY columns (call again, or pass several, to group by more).

lua

    Player:select_raw("faction, COUNT(*) AS n"):group_by("faction"):rows():await()

having method

lua

NormQueryBuilder:having(expr: string, op?: string, value?: any)
  -> self: NormQueryBuilder

Add a HAVING condition (ANDed) on a RAW aggregate expression. Forms: having(expr, value) or having(expr, op, value). The expression is emitted verbatim (so you can reference COUNT(*), SUM(\coins`)`, …); the value is bound.

lua

    Player:select_raw("faction, COUNT(*) AS n"):group_by("faction")
          :having("COUNT(*)", ">", 10):rows():await()

include method

lua

NormQueryBuilder:include(...string|fun(q: NormQueryBuilder))
  -> self: NormQueryBuilder

Eager-load relations with the result (one batched query per relation level — no N+1), attaching them to each returned record. Three forms:

include("posts", "profile") — simple relation names.
include("posts.comments") — nested via a dotted path (shared prefixes load once).
include("posts", function(q) ... end) — with per-relation options: call where / order / limit / offset (and nested include) on q. The limit is applied PER PARENT (e.g. 5 latest posts for each user).

lua

    local users = User:query():include("posts.comments"):all():await()
    print(#users[1].posts[1].comments)

    local u = User:query():include("posts", function(q)
        q:where("published", true):order("created_at", "DESC"):limit(5)
         :include("comments", function(c) c:order("created_at", "ASC") end)
    end):all():await()

increment async method

lua

NormQueryBuilder:increment(column: string, amount?: number)
  -> promise: NormNumberPromise

Atomically add amount (default 1) to a column on every matching row, in one SET col = col + ? statement (no read-modify-write, race-free). Resolves with the affected row count.

lua

    Player:where("id", id):increment("coins", 50):await()

join method

lua

NormQueryBuilder:join(table_name: string, first: string, op: string, second?: string)
  -> self: NormQueryBuilder

INNER JOIN another table. Use qualified table.column refs. Forms: join(table, first, second) (defaults =) or join(table, first, op, second). Joins are for FILTERING/SORTING by a related table — combine with qualified where/order. Since joined rows mix columns from both tables, restrict the projection with :select_raw("main.*") if you still want :all() to wrap the main model, or read the flattened rows with :rows().

lua

    Post:join("users", "users.id", "posts.user_id")
        :where("users.admin", true):select_raw("`posts`.*"):all():await()

left_join method

lua

NormQueryBuilder:left_join(table_name: string, first: string, op: string, second?: string)
  -> self: NormQueryBuilder

LEFT JOIN another table (same argument forms as :join). Keeps main rows even when there is no match on the joined side.

limit method

lua

NormQueryBuilder:limit(n: number)
  -> self: NormQueryBuilder

Limit the number of rows (pair with :offset() for pagination).

lua

    local page = User:query():order("id"):limit(10):offset(20):all():await()

max async method

lua

NormQueryBuilder:max(column: string)
  -> promise: NormNumberPromise

MAX of a column over the current filter. Resolves with the raw value.

lua

    local top = Player:max("score"):await()

min async method

lua

NormQueryBuilder:min(column: string)
  -> promise: NormNumberPromise

MIN of a column over the current filter. Resolves with the raw value.

model field

lua

NormModel

offset method

lua

NormQueryBuilder:offset(n: number)
  -> self: NormQueryBuilder

Skip n rows (use with :limit()).

omit method

lua

NormQueryBuilder:omit(...string|string[])
  -> self: NormQueryBuilder

Inverse of select: select every column of the model EXCEPT the given ones (e.g. to drop a password / large blob without listing all the others). The omitted columns are simply absent from the returned records.

lua

    local u = User:omit("password"):find(1):await()

only_trashed method

lua

NormQueryBuilder:only_trashed()
  -> self: NormQueryBuilder

Return ONLY soft-deleted rows.

or_where method

lua

NormQueryBuilder:or_where(column: string|table<string, any>, op?: string, value?: any)
  -> self: NormQueryBuilder

OR variant of where.

lua

    User:query():where("admin", true):or_where("coins", ">", 1000):all():await()

or_where_between method

lua

NormQueryBuilder:or_where_between(column: string, min: any, max: any)
  -> self: NormQueryBuilder

or_where_in method

lua

NormQueryBuilder:or_where_in(column: string, list: any[])
  -> self: NormQueryBuilder

or_where_like method

lua

NormQueryBuilder:or_where_like(column: string, pattern: string)
  -> self: NormQueryBuilder

or_where_not method

lua

NormQueryBuilder:or_where_not(column: string, value: any)
  -> self: NormQueryBuilder

or_where_not_between method

lua

NormQueryBuilder:or_where_not_between(column: string, min: any, max: any)
  -> self: NormQueryBuilder

or_where_not_in method

lua

NormQueryBuilder:or_where_not_in(column: string, list: any[])
  -> self: NormQueryBuilder

or_where_not_like method

lua

NormQueryBuilder:or_where_not_like(column: string, pattern: string)
  -> self: NormQueryBuilder

or_where_not_null method

lua

NormQueryBuilder:or_where_not_null(column: string)
  -> self: NormQueryBuilder

or_where_null method

lua

NormQueryBuilder:or_where_null(column: string)
  -> self: NormQueryBuilder

order method

lua

NormQueryBuilder:order(column: string, dir?: "ASC"|"DESC")
  -> self: NormQueryBuilder

Add an ORDER BY clause (call again for secondary orderings).

lua

    User:query():order("coins", "DESC"):order("name"):all():await()

lua

dir:
   | "ASC"
   | "DESC"

paginate async method

lua

NormQueryBuilder:paginate(page?: number, per_page?: number)
  -> promise: NormPromise

Paginate the current query. Runs a COUNT(*) over the filter plus a LIMIT/OFFSET page query, resolving with { data, total, page, per_page, last_page, from, to }. Honours where, order, soft-delete scope, and with_count.

lua

    local p = User:where("admin", true):order("name"):paginate(2, 20):await()
    print(p.page, p.last_page, #p.data, p.total)

rows async method

lua

NormQueryBuilder:rows()
  -> promise: NormRowsPromise

Execute the query and resolve with the RAW rows (no record wrapping). Use this for grouped aggregates built with :select_raw / :group_by / :having.

lua

    local stats = Player:select_raw("faction, COUNT(*) AS n, SUM(`coins`) AS total")
        :group_by("faction"):having("COUNT(*)", ">", 10):rows():await()

scope method

lua

NormQueryBuilder:scope(name: string, ...any)
  -> self: NormQueryBuilder

Apply a named scope (a reusable query fragment registered on the model with Model:scope(name, fn)), passing it any extra args. Chainable.

lua

    User:active():scope("older_than", 18):all():await()

select method

lua

NormQueryBuilder:select(...string|string[])
  -> self: NormQueryBuilder

Restrict selected columns (the inverse is :omit).

lua

    User:query():select("id", "name"):all():await()

select_raw method

lua

NormQueryBuilder:select_raw(expr: string)
  -> self: NormQueryBuilder

Add a RAW (unquoted) select expression — for aggregates/computed columns that the column-quoting select can't express. Pair with :group_by and :rows().

lua

    User:select_raw("faction, COUNT(*) AS n"):group_by("faction"):rows():await()

sum async method

lua

NormQueryBuilder:sum(column: string)
  -> promise: NormNumberPromise

SUM of a column over the current filter. Resolves with a number (0 if empty).

lua

    local bank = User:where("admin", false):sum("coins"):await()

update async method

lua

NormQueryBuilder:update(data: table<string, any>)
  -> promise: NormNumberPromise

Bulk-update every matching row in one statement (no records loaded). Resolves with the affected row count.

lua

    local n = User:query():where("admin", true):update({ coins = 0 }):await()

where method

lua

NormQueryBuilder:where(column: string|table<string, any>, op?: string, value?: any)
  -> self: NormQueryBuilder

Add an AND condition. Forms: where(col, value), where(col, op, value) or where({ col = value, ... }). Chainable with the other where_* helpers.

lua

    User:query():where("coins", ">", 100):where("admin", true):all():await()

where_between method

lua

NormQueryBuilder:where_between(column: string, min: any, max: any)
  -> self: NormQueryBuilder

column [NOT] BETWEEN min AND max (inclusive). With OR / negated variants.

lua

    Player:query():where_between("level", 10, 20):all():await()

where_doesnt_have method

lua

NormQueryBuilder:where_doesnt_have(name: string, configure?: fun(q: NormQueryBuilder))
  -> self: NormQueryBuilder

Inverse of where_has: keep only rows with NO matching related row (NOT EXISTS (...)).

where_has method

lua

NormQueryBuilder:where_has(name: string, configure?: fun(q: NormQueryBuilder))
  -> self: NormQueryBuilder

Keep only rows that HAVE at least one related row for name (optionally matching the configure conditions). Compiles to EXISTS (correlated subquery).

lua

    User:where_has("posts"):all():await()                       -- users with any post
    User:where_has("posts", function(q) q:where("published", true) end):all():await()

where_in method

lua

NormQueryBuilder:where_in(column: string, list: any[])
  -> self: NormQueryBuilder

column IN (...) (and its OR / negated variants).

lua

    User:query():where_in("id", { 1, 2, 3 }):all():await()

where_like method

lua

NormQueryBuilder:where_like(column: string, pattern: string)
  -> self: NormQueryBuilder

column [NOT] LIKE pattern (use % / _ wildcards). With OR / negated variants.

lua

    User:query():where_like("name", "John%"):all():await()

where_not method

lua

NormQueryBuilder:where_not(column: string, value: any)
  -> self: NormQueryBuilder

column != value (and OR variant).

where_not_between method

lua

NormQueryBuilder:where_not_between(column: string, min: any, max: any)
  -> self: NormQueryBuilder

where_not_in method

lua

NormQueryBuilder:where_not_in(column: string, list: any[])
  -> self: NormQueryBuilder

where_not_like method

lua

NormQueryBuilder:where_not_like(column: string, pattern: string)
  -> self: NormQueryBuilder

where_not_null method

lua

NormQueryBuilder:where_not_null(column: string)
  -> self: NormQueryBuilder

where_null method

lua

NormQueryBuilder:where_null(column: string)
  -> self: NormQueryBuilder

column IS [NOT] NULL (and OR variants).

with_count method

lua

NormQueryBuilder:with_count(...string)
  -> self: NormQueryBuilder

Add a <name>_count field to each returned record: the number of related rows, without loading them (a correlated COUNT(*) subquery). Soft-deleted related rows aren't counted.

lua

    local users = User:with_count("posts"):all():await()
    print(users[1].posts_count)

with_trashed method

lua

NormQueryBuilder:with_trashed()
  -> self: NormQueryBuilder

Include soft-deleted rows in the result (disables the default exclusion).

NormQueryBuilder class ​

all async method ​

avg async method ​

count async method ​

decrement async method ​

delete async method ​

first async method ​

force_delete async method ​

group_by method ​

having method ​

include method ​

increment async method ​

join method ​

left_join method ​

limit method ​

max async method ​

min async method ​

model field ​

offset method ​

omit method ​

only_trashed method ​

or_where method ​

or_where_between method ​

or_where_in method ​

or_where_like method ​

or_where_not method ​

or_where_not_between method ​

or_where_not_in method ​

or_where_not_like method ​

or_where_not_null method ​

or_where_null method ​

order method ​

paginate async method ​

rows async method ​

scope method ​

select method ​

select_raw method ​

sum async method ​

update async method ​

where method ​

where_between method ​

where_doesnt_have method ​

where_has method ​

where_in method ​

where_like method ​

where_not method ​

where_not_between method ​

where_not_in method ​

where_not_like method ​

where_not_null method ​

where_null method ​

with_count method ​

with_trashed method ​