Documentation / zerotal / orm / ModelQueryBuilder

Class: ModelQueryBuilder<M>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:325

The model-aware query builder returned by Model.query().

Extends the low-level QueryBuilder with everything that makes a query "Active Record": rows are hydrated into model instances, relations can be eager-loaded, relationship-existence filters (has / whereHas) and relation aggregates (withCount / withSum / …) are available, named and global scopes are applied, and terminal methods (get, first, firstOrFail, findOrFail, paginate) return model instances instead of raw rows.

Remarks

Behaviours layered on top of QueryBuilder:

Model hydration — get() / first() map each result row through Model.fromRow(), apply casts, run the afterFind hook, and attach any relation aggregate columns (e.g. commentsCount, commentsExists).
Column-name resolution — every column-taking method (where, orderBy, select, sum, …) accepts a model property name in camelCase and resolves it to the snake_case database column, so where('createdAt', …) targets created_at.
Eager loading — with declares relations (including nested dot-paths and constrained closures) to load in a batched follow-up query.
Relationship existence — has / whereHas / doesntHave filter the parent rows by correlated EXISTS subqueries.
Global scopes — scopes registered on the model (and inherited through the prototype chain) are applied lazily on the first terminal call; opt out per query with withoutGlobalScope / withoutGlobalScopes.
Soft-delete scoping asymmetry — has, whereHas, withCount and the other relation aggregates exclude soft-deleted related rows (they add deleted_at IS NULL when the related model soft-deletes), but eager with loads related rows through the model's unscoped query and therefore does not apply the related model's soft-delete (or global) scopes. Trashed related records will appear in an eager-loaded relation.

Example

// Hydrated User instances, each with its posts eager-loaded, paginated.
const page = await User.query()
  .with('posts', (q) => q.where('published', true))
  .where('active', true)
  .orderBy('createdAt', 'desc')
  .paginate(15, 1); // 15 per page, page 1

for (const user of page.data) {
  console.log(user.name, user.posts.length);
}

Extends

QueryBuilder

Type Parameters

M

M extends BaseModel

The model class this builder queries and hydrates.

Constructors

Constructor

new ModelQueryBuilder<M>(table, sql, ModelClass): ModelQueryBuilder<M>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:334

Parameters

table

string

sql

SQLInstance

ModelClass

typeof BaseModel

Returns

ModelQueryBuilder<M>

Overrides

QueryBuilder.constructor

Aggregates

count()

count(): Promise<number>

Defined in: packages/orm/src/db/QueryBuilder.ts:1374

COUNT(*) over the current query, ignoring any select list. Returns 0 when there are no rows.

Counts what the query returns. A grouped query returns one row per group, so groupBy("country").count() is the number of countries, not the number of rows — the previous implementation dropped the grouping and answered 1, which also made paginate() report total: 1 beside two rows of data. DISTINCT is likewise honoured rather than forced off. Those cases count through a subquery, which is the only form that gets both right.

ORDER BY is always dropped: it cannot change a count, and retaining it makes the count query illegal under PostgreSQL and MySQL's ONLY_FULL_GROUP_BY — so orderBy(...).paginate(), the most common call in the framework, could not run there at all.

Returns

Promise<number>

Inherited from

QueryBuilder.count

sum()

sum(column): Promise<number>

Defined in: packages/orm/src/db/QueryBuilder.ts:1386

SUM(column), coerced to a number (0 when the sum is NULL/no rows).

Parameters

column

string

Returns

Promise<number>

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.sum

avg()

avg(column): Promise<number>

Defined in: packages/orm/src/db/QueryBuilder.ts:1400

AVG(column), coerced to a number (0 when NULL/no rows).

Parameters

column

string

Returns

Promise<number>

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.avg

min()

min(column): Promise<number>

Defined in: packages/orm/src/db/QueryBuilder.ts:1414

MIN(column), coerced to a number (0 when NULL/no rows).

Parameters

column

string

Returns

Promise<number>

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.min

max()

max(column): Promise<number>

Defined in: packages/orm/src/db/QueryBuilder.ts:1428

MAX(column), coerced to a number (0 when NULL/no rows).

Parameters

column

string

Returns

Promise<number>

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.max

exists()

exists(): Promise<boolean>

Defined in: packages/orm/src/db/QueryBuilder.ts:1442

Whether at least one row matches. Runs SELECT 1 … LIMIT 1 and restores the previous limit/select state afterward.

Returns

Promise<boolean>

Inherited from

QueryBuilder.exists

doesntExist()

doesntExist(): Promise<boolean>

Defined in: packages/orm/src/db/QueryBuilder.ts:1457

Inverse of exists — true when no rows match.

Returns

Promise<boolean>

Inherited from

QueryBuilder.doesntExist

withCount()

withCount(relation, constraint?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:468

Add a COUNT subquery for a relation, injected as <relation>Count (camelCase) on every hydrated result.

Supports hasMany, hasOne, belongsTo and manyToMany relations, and an optional constraint closure. The count excludes soft-deleted related rows when the related model soft-deletes.

Parameters

relation

string | string[] | Record<string, RelationConstraint>

A relation name, an array of names, or a { name: constraint } map to count several relations (optionally constrained) at once.

constraint?

RelationConstraint

Optional closure narrowing which related rows are counted (applies when relation is a single string).

Returns

this

This builder for chaining.

Example

const posts = await Post.query().withCount('comments').get();
// posts[0].commentsCount === 12

await Post.query()
  .withCount({ comments: (q) => q.where('approved', true) })
  .get();

withSum()

withSum(relation, column, constraint?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:495

Add a SUM(column) subquery for a relation, injected as <relation>Sum<Column> (camelCase) on every hydrated result.

Parameters

relation

string

The relation to aggregate over.

column

string

The related-table column to sum.

constraint?

RelationConstraint

Optional closure narrowing which related rows are summed.

Returns

this

This builder for chaining.

Example

const posts = await Post.query().withSum('comments', 'votes').get();
// posts[0].commentsSumVotes === 42

withAvg()

withAvg(relation, column, constraint?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:514

Add an AVG(column) subquery for a relation, injected as <relation>Avg<Column> (camelCase) on every hydrated result.

Parameters

relation

string

The relation to aggregate over.

column

string

The related-table column to average.

constraint?

RelationConstraint

Optional closure narrowing which related rows are averaged.

Returns

this

This builder for chaining.

withMin()

withMin(relation, column, constraint?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:533

Add a MIN(column) subquery for a relation, injected as <relation>Min<Column> (camelCase) on every hydrated result.

Parameters

relation

string

The relation to aggregate over.

column

string

The related-table column to take the minimum of.

constraint?

RelationConstraint

Optional closure narrowing which related rows are considered.

Returns

this

This builder for chaining.

withMax()

withMax(relation, column, constraint?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:552

Add a MAX(column) subquery for a relation, injected as <relation>Max<Column> (camelCase) on every hydrated result.

Parameters

relation

string

The relation to aggregate over.

column

string

The related-table column to take the maximum of.

constraint?

RelationConstraint

Optional closure narrowing which related rows are considered.

Returns

this

This builder for chaining.

withExists()

withExists(relation, constraint?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:578

Add an EXISTS subquery for a relation, injected as <relation>Exists (boolean) on every result — a single-query check that avoids loading the related rows. Optional constraint narrows what counts as "existing".

Parameters

relation

string

The relation to test for existence.

constraint?

RelationConstraint

Optional closure narrowing what counts as "existing".

Returns

this

This builder for chaining.

Example

const posts = await Post.query().withExists('comments').get();
// posts[0].commentsExists === true | false

Eager loading

with()

Call Signature

with<K>(relation): ModelQueryBuilder<Omit<M, K> & { [K in string]: M[K] extends ManyToMany<T> ? ManyToMany<T> : M[K] extends HasMany<T> ? T[] : M[K] extends BelongsTo<T> ? T | null : M[K] extends HasOne<T> ? T | null : M[K] extends MorphTo<T> ? T | null : M[K] extends MorphMany<T> ? T[] : (...)[(...)] extends MorphOne<(...)> ? (...) | (...) : (...)[(...)] } & BaseModel>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1081

Declare one or more relations to eager-load in a batched follow-up query.

Accepts a bare name, a nested dot-path, a name plus a constraint closure, an array mixing names and { name: constraint } maps, or a single such map:

.with('comments')
.with('author.profile')                       // nested (dot)
.with('comments', (q) => q.where('ok', true)) // constrained
.with({ comments: (q) => q.where('ok', true) })
.with(['author', { comments: (q) => q.where('ok', true) }])

The single-bare-string overload narrows the builder's type so callers keep fully-typed access to the loaded relation.

Type Parameters

K

K extends string

Parameters

relation

K

Relation name, dot-path, array, or { name: constraint } map.

Returns

ModelQueryBuilder<Omit<M, K> & { [K in string]: M[K] extends ManyToMany<T> ? ManyToMany<T> : M[K] extends HasMany<T> ? T[] : M[K] extends BelongsTo<T> ? T | null : M[K] extends HasOne<T> ? T | null : M[K] extends MorphTo<T> ? T | null : M[K] extends MorphMany<T> ? T[] : (...)[(...)] extends MorphOne<(...)> ? (...) | (...) : (...)[(...)] } & BaseModel>

This builder (type-narrowed for the single-string overload).

Remarks

Eager loads run through the related model's unscoped query, so the related model's soft-delete and global scopes are not applied — trashed related rows are included. Constraint closures may add their own filters. Contrast with has / withCount, which do exclude soft-deleted related rows.

Example

const users = await User.query()
  .with('posts', (q) => q.where('published', true))
  .with('profile')
  .get();

Call Signature

with(relation, constraint): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1082

Declare one or more relations to eager-load in a batched follow-up query.

Accepts a bare name, a nested dot-path, a name plus a constraint closure, an array mixing names and { name: constraint } maps, or a single such map:

.with('comments')
.with('author.profile')                       // nested (dot)
.with('comments', (q) => q.where('ok', true)) // constrained
.with({ comments: (q) => q.where('ok', true) })
.with(['author', { comments: (q) => q.where('ok', true) }])

The single-bare-string overload narrows the builder's type so callers keep fully-typed access to the loaded relation.

Parameters

relation

string

Relation name, dot-path, array, or { name: constraint } map.

constraint

RelationConstraint

Optional constraint closure (single-string form only).

Returns

this

This builder (type-narrowed for the single-string overload).

Remarks

Example

const users = await User.query()
  .with('posts', (q) => q.where('published', true))
  .with('profile')
  .get();

Call Signature

with(relations): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1083

Declare one or more relations to eager-load in a batched follow-up query.

Accepts a bare name, a nested dot-path, a name plus a constraint closure, an array mixing names and { name: constraint } maps, or a single such map:

.with('comments')
.with('author.profile')                       // nested (dot)
.with('comments', (q) => q.where('ok', true)) // constrained
.with({ comments: (q) => q.where('ok', true) })
.with(['author', { comments: (q) => q.where('ok', true) }])

The single-bare-string overload narrows the builder's type so callers keep fully-typed access to the loaded relation.

Parameters

relations

(string | Record<string, RelationConstraint>)[]

Returns

this

This builder (type-narrowed for the single-string overload).

Remarks

Example

const users = await User.query()
  .with('posts', (q) => q.where('published', true))
  .with('profile')
  .get();

Call Signature

with(map): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1084

Declare one or more relations to eager-load in a batched follow-up query.

Accepts a bare name, a nested dot-path, a name plus a constraint closure, an array mixing names and { name: constraint } maps, or a single such map:

.with('comments')
.with('author.profile')                       // nested (dot)
.with('comments', (q) => q.where('ok', true)) // constrained
.with({ comments: (q) => q.where('ok', true) })
.with(['author', { comments: (q) => q.where('ok', true) }])

The single-bare-string overload narrows the builder's type so callers keep fully-typed access to the loaded relation.

Parameters

map

Record<string, RelationConstraint | true>

Returns

this

This builder (type-narrowed for the single-string overload).

Remarks

Example

const users = await User.query()
  .with('posts', (q) => q.where('published', true))
  .with('profile')
  .get();

Execution

pluck()

pluck<V>(column, key?): Promise<Record<string, V> | V[]>

Defined in: packages/orm/src/db/QueryBuilder.ts:1314

Return an array of a single column's values. Pass key to return an object keyed by that column instead.

Type Parameters

V

V = unknown

Parameters

column

string

key?

string

Returns

Promise<Record<string, V> | V[]>

Example

await DB.table('users').pluck('email');            // ['a@x', 'b@y']
await DB.table('users').pluck('name', 'id');       // { 1: 'Al', 2: 'Bo' }

Inherited from

QueryBuilder.pluck

value()

value<V>(column): Promise<V | null>

Defined in: packages/orm/src/db/QueryBuilder.ts:1344

Return a single column's value from the first matching row, or null.

Type Parameters

V

V = unknown

Parameters

column

string

Returns

Promise<V | null>

Inherited from

QueryBuilder.value

sole()

sole<T>(): Promise<T>

Defined in: packages/orm/src/db/QueryBuilder.ts:1466

Return the single matching row, asserting uniqueness.

Type Parameters

T

T = Record<string, unknown>

Returns

Promise<T>

Throws

When zero rows match, or when more than one row matches.

Inherited from

QueryBuilder.sole

chunk()

chunk<T>(size, callback): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1602

Process results in fixed-size pages (offset-based). Return false from the callback to stop early. Memory-safe for large tables.

Type Parameters

T

T = Record<string, unknown>

Parameters

size

number

callback

(rows, page) => unknown

Returns

Promise<void>

Inherited from

QueryBuilder.chunk

chunkById()

chunkById<T>(size, callback, column?): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1626

Like chunk() but pages by an incrementing key (keyset). Stable when rows are inserted/deleted during iteration. column defaults to id.

Type Parameters

T

T = Record<string, unknown>

Parameters

size

number

callback

(rows) => unknown

column?

string = "id"

Returns

Promise<void>

Inherited from

QueryBuilder.chunkById

lazy()

lazy<T>(size?): AsyncGenerator<T>

Defined in: packages/orm/src/db/QueryBuilder.ts:1652

Async generator yielding one row at a time (offset-paged internally).

Type Parameters

T

T = Record<string, unknown>

Parameters

size?

number = 1000

Returns

AsyncGenerator<T>

Inherited from

QueryBuilder.lazy

lazyById()

lazyById<T>(size?, column?): AsyncGenerator<T>

Defined in: packages/orm/src/db/QueryBuilder.ts:1672

Async generator yielding one row at a time (keyset-paged on column, default id).

Type Parameters

T

T = Record<string, unknown>

Parameters

size?

number = 1000

column?

string = "id"

Returns

AsyncGenerator<T>

Inherited from

QueryBuilder.lazyById

cursor()

cursor<T>(size?): AsyncGenerator<T>

Defined in: packages/orm/src/db/QueryBuilder.ts:1693

Alias of lazy — stream rows one at a time.

Type Parameters

T

T = Record<string, unknown>

Parameters

size?

number = 1000

Returns

AsyncGenerator<T>

Inherited from

QueryBuilder.cursor

each()

each<T>(callback, size?): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1702

Invoke callback for each row, streaming in pages. Return false from the callback to stop early.

Type Parameters

T

T = Record<string, unknown>

Parameters

callback

(row, index) => unknown

size?

number = 1000

Returns

Promise<void>

Inherited from

QueryBuilder.each

toSql()

toSql(): string

Defined in: packages/orm/src/db/QueryBuilder.ts:1719

Compiled SELECT SQL with ? placeholders. Does not execute.

Returns

string

Inherited from

QueryBuilder.toSql

toSqlWithBindings()

toSqlWithBindings(): object

Defined in: packages/orm/src/db/QueryBuilder.ts:1727

{ sql, bindings } for the current SELECT. Does not execute.

Returns

object

sql

sql: string

bindings

bindings: unknown[]

Inherited from

QueryBuilder.toSqlWithBindings

toRawSql()

toRawSql(): string

Defined in: packages/orm/src/db/QueryBuilder.ts:1736

SQL with bindings inlined as literals — for logging only. The result is not safe to execute (values are not re-escaped for a driver).

Returns

string

Inherited from

QueryBuilder.toRawSql

dump()

dump(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1747

Log the compiled SQL and bindings to the console, then return the builder for continued chaining.

Returns

this

Inherited from

QueryBuilder.dump

dd()

dd(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1759

Alias of dump. Note: despite the conventional dd() name, this does NOT dump-and-die — it logs and returns for chaining.

Returns

this

Inherited from

QueryBuilder.dd

explain()

explain<T>(): Promise<T[]>

Defined in: packages/orm/src/db/QueryBuilder.ts:1768

Run the query plan for the current SELECT and return the plan rows — EXPLAIN QUERY PLAN on SQLite, EXPLAIN elsewhere.

Type Parameters

T

T = Record<string, unknown>

Returns

Promise<T[]>

Inherited from

QueryBuilder.explain

Insert / update / delete

insert()

insert(data): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1487

Insert a single row. Object keys become columns (each asserted); values are bound. An empty object is a no-op. Ignores any WHERE clauses on the builder.

Parameters

data

Record<string, unknown>

Returns

Promise<void>

Throws

When any column key is not a safe SQL identifier.

Example

await DB.table('users').insert({ email: 'a@example.com', active: true });

Inherited from

QueryBuilder.insert

update()

update(data): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1513

UPDATE … SET … for rows matching the current WHERE clauses. Object keys become assigned columns (each asserted); values are bound. An empty object is a no-op.

Parameters

data

Record<string, unknown>

Returns

Promise<void>

Throws

When any column key is not a safe SQL identifier.

Example

await DB.table('users').where('id', 1).update({ active: false });

Inherited from

QueryBuilder.update

updateOrInsert()

updateOrInsert(attributes, values?): Promise<boolean>

Defined in: packages/orm/src/db/QueryBuilder.ts:1537

Update rows matching attributes; insert a merged { ...attributes, ...values } row when none exist.

Parameters

attributes

Record<string, unknown>

values?

Record<string, unknown> = {}

Returns

Promise<boolean>

true when a row was inserted, false when an existing row was updated.

Inherited from

QueryBuilder.updateOrInsert

delete()

delete(): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1558

DELETE FROM … for rows matching the current WHERE clauses.

Returns

Promise<void>

Remarks

With no WHERE clauses this deletes every row in the table.

Inherited from

QueryBuilder.delete

increment()

increment(column, amount?): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1571

Atomically add amount (default 1) to column for matching rows (SET column = column + ?).

Parameters

column

string

amount?

number = 1

Returns

Promise<void>

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.increment

decrement()

decrement(column, amount?): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1586

Atomically subtract amount (default 1) from column for matching rows (SET column = column - ?).

Parameters

column

string

amount?

number = 1

Returns

Promise<void>

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.decrement

Joins

join()

join(table, first, operator, second): this

Defined in: packages/orm/src/db/QueryBuilder.ts:991

INNER JOIN table ON first <operator> second. The table and both columns are asserted; the operator is checked against the column-comparison set.

Parameters

table

string

first

string

operator

string

second

string

Returns

this

Throws

When an identifier is unsafe or the operator is unsupported.

Example

DB.table('users').join('orders', 'users.id', '=', 'orders.user_id');

Inherited from

QueryBuilder.join

leftJoin()

leftJoin(table, first, operator, second): this

Defined in: packages/orm/src/db/QueryBuilder.ts:999

LEFT JOIN. See join.

Parameters

table

string

first

string

operator

string

second

string

Returns

this

Throws

When an identifier is unsafe or the operator is unsupported.

Inherited from

QueryBuilder.leftJoin

rightJoin()

rightJoin(table, first, operator, second): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1007

RIGHT JOIN. See join.

Parameters

table

string

first

string

operator

string

second

string

Returns

this

Throws

When an identifier is unsafe or the operator is unsupported.

Inherited from

QueryBuilder.rightJoin

crossJoin()

crossJoin(table): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1015

CROSS JOIN table. Only the table name is asserted.

Parameters

table

string

Returns

this

Throws

When table is not a safe SQL identifier.

Inherited from

QueryBuilder.crossJoin

joinSub()

joinSub(sub, alias, first, operator, second, type?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1040

Join a subquery, aliased. Pass a builder (or a callback that populates one) plus an alias and the join condition. The subquery's bindings are merged into the parent query.

Parameters

sub

QueryBuilder | ((q) => void)

alias

string

first

string

operator

string

second

string

type?

JoinType = "inner"

Returns

this

Remarks

The alias, first and second are asserted as safe identifiers and operator against the allowed column-comparison set — the same guards join applies. Only the subquery's own SQL comes from the passed builder.

Example

qb.joinSub(
  DB.table('orders').selectRaw('user_id, COUNT(*) c').groupBy('user_id'),
  'o', 'users.id', '=', 'o.user_id',
);

Inherited from

QueryBuilder.joinSub

Ordering & grouping

orderBy()

orderBy(column, direction?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:870

Order by a column. The column must be a safe identifier — for raw SQL expressions (e.g. RANDOM()) use orderByRaw.

Parameters

column

string

direction?

OrderDirection = "asc"

'asc' (default) or 'desc', any casing.

Returns

this

Throws

When column is not a safe SQL identifier, or direction is neither asc nor desc — the OrderDirection type is a compile-time hint, and a direction read off a query string arrives as an unchecked string.

Inherited from

QueryBuilder.orderBy

orderByDesc()

orderByDesc(column): this

Defined in: packages/orm/src/db/QueryBuilder.ts:882

Order descending by a column. Shorthand for orderBy(column, 'desc').

Parameters

column

string

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orderByDesc

desc()

desc(column?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:891

Order descending by a column (default the primary key id).

Parameters

column?

string = "id"

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.desc

asc()

asc(column?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:900

Order ascending by a column (default the primary key id).

Parameters

column?

string = "id"

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.asc

latest()

latest(column?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:909

Order newest-first by a timestamp column (default created_at).

Parameters

column?

string = "created_at"

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.latest

oldest()

oldest(column?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:918

Order oldest-first by a timestamp column (default created_at).

Parameters

column?

string = "created_at"

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.oldest

inRandomOrder()

inRandomOrder(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:926

Randomise row order — RAND() on MySQL, RANDOM() on SQLite/Postgres.

Returns

this

Inherited from

QueryBuilder.inRandomOrder

reorder()

reorder(column?, direction?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:935

Clear all ORDER BY clauses; optionally apply a new one.

Parameters

column?

string

direction?

OrderDirection = "asc"

Returns

this

Throws

When a replacement column is given and is unsafe.

Inherited from

QueryBuilder.reorder

groupBy()

groupBy(...columns): this

Defined in: packages/orm/src/db/QueryBuilder.ts:946

Add columns to the GROUP BY clause. Each column is asserted.

Parameters

columns

...string[]

Returns

this

Throws

When any column is not a safe SQL identifier.

Inherited from

QueryBuilder.groupBy

having()

having(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:966

Add a HAVING clause. The column may be a plain identifier or a simple aggregate such as SUM(score); the value is always bound. Two-arg form defaults the operator to =. Multiple HAVING clauses are joined with AND.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is neither a safe identifier nor a simple aggregate, or when the operator is unsupported.

Example

DB.table('orders').groupBy('user_id').having('SUM(total)', '>', 100);

Inherited from

QueryBuilder.having

union()

union(other, all?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1097

Append UNION (or UNION ALL when all is true) with another builder's compiled SELECT. The other query's bindings are merged.

Parameters

other

QueryBuilder

all?

boolean = false

Returns

this

Inherited from

QueryBuilder.union

unionAll()

unionAll(other): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1107

Append UNION ALL with another builder's SELECT. Shorthand for union(other, true).

Parameters

other

QueryBuilder

Returns

this

Inherited from

QueryBuilder.unionAll

Other

_state

protected _state: QueryState

Defined in: packages/orm/src/db/QueryBuilder.ts:316

Inherited from

QueryBuilder._state

_sql

protected _sql: SQLInstance

Defined in: packages/orm/src/db/QueryBuilder.ts:317

Inherited from

QueryBuilder._sql

_userWhereStart

protected _userWhereStart: number = 0

Defined in: packages/orm/src/db/QueryBuilder.ts:327

Index into _state.wheres at which caller-supplied predicates begin.

Anything before it was injected by the framework (currently the soft-delete predicate seeded in BaseModel.query()) and must stay outside the group built by _groupUserWheres. Defaults to 0 for a plain DB.table() builder, which has no framework predicates.

Inherited from

QueryBuilder._userWhereStart

_groupUserWheres()

protected _groupUserWheres(): void

Defined in: packages/orm/src/db/QueryBuilder.ts:2288

Returns

void

Inherited from

QueryBuilder._groupUserWheres

_newInstance()

protected _newInstance(): QueryBuilder

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:339

Returns

QueryBuilder

Overrides

QueryBuilder._newInstance

_column()

protected _column(column): string

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:884

Identifier-ingress override: resolve a model property name to its database column name (camelCase → snake_case). The base builder routes every caller-supplied column through this one hook, so resolution covers all column-taking methods — where/orderBy/select and equally whereNotIn, whereAny, pluck, increment, join columns and the cursor/keyset pagination option columns, which the 29 per-method overrides this hook replaces had drifted on. Idempotent for already-snake and qualified columns; raw expressions pass through untouched.

Parameters

column

string

Returns

string

Overrides

QueryBuilder._column

_bind()

protected _bind(column, value, operator?): unknown

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:894

Value-ingress override: coerce a bound value through the column's cast metadata (Carbon → DB string, boolean → 0/1, custom cast setters), so a value compares in its stored representation everywhere a column-value pair enters the builder — where, whereIn and now whereBetween alike.

Parameters

column

string

value

unknown

operator?

WhereOperator

Returns

unknown

Overrides

QueryBuilder._bind

Pagination

limit()

limit(n): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1225

Cap the number of rows returned (LIMIT). The value is bound.

Parameters

n

number

Returns

this

Inherited from

QueryBuilder.limit

offset()

offset(n): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1234

Skip a number of leading rows (OFFSET). The value is bound.

Parameters

n

number

Returns

this

Inherited from

QueryBuilder.offset

paginate()

paginate<T>(perPage?, page?, pageName?): Promise<PaginateResult<T>>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1173

Model-aware length-aware pagination — returns hydrated model instances (not raw rows) and honours eager-load chains set via with.

Runs a count() for the total, then fetches the page via limit/offset. page and perPage are clamped to a minimum of 1.

Type Parameters

T

T = M

Element type of the page data (defaults to the model M).

Parameters

perPage?

number = 15

Rows per page (default 15).

page?

number

1-based page number (default 1).

pageName?

string = "page"

Returns

Promise<PaginateResult<T>>

A paginate result with data, total, page, perPage, lastPage and the standard pagination helpers.

Example

const page = await User.query().with('posts').paginate(20, 2);
console.log(page.total, page.lastPage, page.data.length);

Overrides

QueryBuilder.paginate

simplePaginate()

simplePaginate<T>(perPage?, page?): Promise<SimplePaginateResult<T>>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1194

Model-aware QueryBuilder.simplePaginate — returns model instances (not raw rows). Defaults the result element type to the model class.

Type Parameters

T

T = M

Element type of the page data (defaults to the model M).

Parameters

perPage?

number = 15

Rows per page (default 15).

page?

number = 1

1-based page number (default 1).

Returns

Promise<SimplePaginateResult<T>>

Remarks

Argument order is (perPage, page), matching paginate.

Overrides

QueryBuilder.simplePaginate

cursorPaginate()

cursorPaginate<T>(options?): Promise<CursorPaginateResult<T>>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1206

Model-aware QueryBuilder.cursorPaginate — returns model instances (not raw rows). Defaults the result element type to the model class.

Type Parameters

T

T = M

Element type of the page data (defaults to the model M).

Parameters

options?

Optional cursor (last seen id) and limit.

cursor?

number

limit?

number

Returns

Promise<CursorPaginateResult<T>>

Overrides

QueryBuilder.cursorPaginate

keysetPaginate()

keysetPaginate<T>(options?): Promise<KeysetPaginateResult<T>>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1222

Model-aware QueryBuilder.keysetPaginate — returns model instances (not raw rows), with casts applied, hidden stripped, eager loads run and global scopes honoured, because it now goes through get() like every other terminal.

Type Parameters

T

T = M

Element type of the page data (defaults to the model M).

Parameters

options?

KeysetOptions

Sort column, direction, limit and opaque cursor.

Returns

Promise<KeysetPaginateResult<T>>

Overrides

QueryBuilder.keysetPaginate

Raw

selectRaw()

selectRaw(expression): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1145

Add a raw SQL expression to the SELECT list.

Security: The expression is injected verbatim into the query — never interpolate user-controlled values directly. Build the expression from trusted constants only, and pass dynamic values via whereRaw() bindings.

Parameters

expression

string

Returns

this

Example

qb.selectRaw('COUNT(*) as total, MAX(score) as top')
qb.selectRaw('price * quantity as revenue')

Inherited from

QueryBuilder.selectRaw

whereRaw()

whereRaw(sql, bindings?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1159

Add a raw SQL WHERE clause. Bindings are passed as the second argument to avoid SQL injection.

Parameters

sql

string

bindings?

unknown[] = []

Returns

this

Example

qb.whereRaw('LOWER(email) = ?', ['alice@example.com'])
qb.whereRaw('score BETWEEN ? AND ?', [10, 50])

Inherited from

QueryBuilder.whereRaw

orWhereRaw()

orWhereRaw(sql, bindings?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1168

OR form of whereRaw. The SQL fragment is trusted-input only; pass dynamic values via bindings.

Parameters

sql

string

bindings?

unknown[] = []

Returns

this

Inherited from

QueryBuilder.orWhereRaw

orderByRaw()

orderByRaw(expression): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1216

Add a raw SQL ORDER BY expression.

Parameters

expression

string

Returns

this

Remarks

Injected verbatim — trusted-input only. Prefer orderBy for plain column ordering.

Example

qb.orderByRaw('RAND()')
qb.orderByRaw('created_at DESC, id ASC')

Inherited from

QueryBuilder.orderByRaw

Relationship constraints

has()

has(relation, operator?, count?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:618

Filter to parent rows that HAVE the related records, via a correlated EXISTS subquery (soft-deleted related rows are excluded).

Supported for hasMany, hasOne, belongsTo, manyToMany, morphMany and morphOne relations. The *Through, morphToMany, morphedByMany and morphTo relation types are not supported and throw — use eager with instead.

Parameters

relation

string

The relation that must exist.

operator?

string

Optional comparison operator against the related count (e.g. '>=').

count?

number

Optional count to compare against (defaults to 1 when an operator is given).

Returns

this

This builder for chaining.

Throws

Error if the relation is undefined on the model, or is a *Through / polymorphic-many / morphTo relation.

Example

Post.query().has('comments');            // at least one comment
Post.query().has('comments', '>=', 3);   // three or more

orHas()

orHas(relation, operator?, count?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:627

OR variant of has — combines with the previous condition via OR.

Parameters

relation

string

operator?

string

count?

number

Returns

this

doesntHave()

doesntHave(relation, callback?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:640

Filter to parent rows that do NOT have the related records (NOT EXISTS).

Parameters

relation

string

The relation that must be absent.

callback?

RelationConstraint

Optional closure constraining which related rows count as present.

Returns

this

This builder for chaining.

orDoesntHave()

orDoesntHave(relation, callback?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:647

OR variant of doesntHave.

Parameters

relation

string

callback?

RelationConstraint

Returns

this

whereHas()

whereHas(relation, callback?, operator?, count?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:665

Filter by existence of related records matching the callback constraints.

Parameters

relation

string

The relation to test.

callback?

RelationConstraint

Optional closure applied to the related subquery.

operator?

string

Optional operator to compare the matching related count against.

count?

number

Optional count to compare against.

Returns

this

This builder for chaining.

Example

User.query().whereHas('posts', (q) => q.where('published', true)).get();

orWhereHas()

orWhereHas(relation, callback?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:677

OR variant of whereHas.

Parameters

relation

string

callback?

RelationConstraint

Returns

this

whereDoesntHave()

whereDoesntHave(relation, callback?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:684

Filter to parent rows with NO related records matching the callback constraints.

Parameters

relation

string

callback?

RelationConstraint

Returns

this

orWhereDoesntHave()

orWhereDoesntHave(relation, callback?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:691

OR variant of whereDoesntHave.

Parameters

relation

string

callback?

RelationConstraint

Returns

this

whereRelation()

whereRelation(relation, column, operatorOrValue, value?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:709

Shorthand for whereHas with a single column condition on the related table.

Parameters

relation

string

The relation to test.

column

string

The related-table column to compare.

operatorOrValue

unknown

The operator, or the value when the 3-arg form is used.

value?

unknown

The value when an explicit operator is supplied.

Returns

this

This builder for chaining.

Example

Post.query().whereRelation('comments', 'approved', true).get();

orWhereRelation()

orWhereRelation(relation, column, operatorOrValue, value?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:720

OR variant of whereRelation.

Parameters

relation

string

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

withWhereHas()

withWhereHas(relation, callback?): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:743

whereHas plus eager-loading the same relation with the same constraint — filter parents by matching related rows and load exactly those rows in one call.

Parameters

relation

string

The relation to filter by and eager-load.

callback?

RelationConstraint

Optional closure applied both to the existence subquery and the eager-load query.

Returns

this

This builder for chaining.

Retrieval

clone()

clone(): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:364

Return a deep copy of this builder, including its eager-load specs, relation aggregate/count/exists entries and excluded-scope set. The copy has scopes un-applied so they run on its own first terminal call.

Returns

this

Overrides

QueryBuilder.clone

get()

get<T>(): Promise<T[]>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:914

Execute the query and return an array of hydrated model instances.

Applies global scopes, injects any withCount / withSum / withExists aggregate columns, hydrates each row via Model.fromRow (attaching the aggregate values as camelCase attributes), performs any eager loads declared with with, and runs the afterFind hook per instance.

Type Parameters

T

T = M

Element type of the returned array (defaults to the model M).

Returns

Promise<T[]>

The matched model instances.

Overrides

QueryBuilder.get

first()

first<T>(): Promise<T | null>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:996

Execute the query and return the first matching model instance, or null.

Applies global scopes, hydrates the row, runs any eager loads declared with with, and runs the afterFind hook.

Type Parameters

T

T = M

Result type (defaults to the model M).

Returns

Promise<T | null>

The first matching instance, or null when none match.

Overrides

QueryBuilder.first

findOrFail()

findOrFail(id): Promise<M>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1022

Find a single instance by primary key, or throw when it does not exist.

Honours any constraints, scopes and eager loads already set on the builder.

Parameters

id

number

Primary-key value to look up.

Returns

Promise<M>

The matching model instance.

Throws

ModelNotFoundError when no row has that primary key.

firstOrFail()

firstOrFail(): Promise<M>

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1037

Return the first matching instance, or throw when none match.

Returns

Promise<M>

The first matching model instance.

Throws

ModelNotFoundError when the query matches no rows.

Scopes

withoutGlobalScope()

withoutGlobalScope(...names): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:388

Exclude one or more named global scopes from this query.

Parameters

names

...string[]

Names of the global scopes to skip for this query only.

Returns

this

This builder for chaining.

Example

// Skip the 'published' scope for this query only
Post.query().withoutGlobalScope('published').get();

withoutGlobalScopes()

withoutGlobalScopes(): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:399

Remove ALL global scopes registered on this model for this query.

Returns

this

This builder for chaining.

withScopes()

withScopes(callback): this

Defined in: packages/orm/src/model/ModelQueryBuilder.ts:1134

Apply one or more named scopes defined as static methods on the model.

The callback receives a proxy whose methods mirror the model's static scope methods; each returns a scope object whose apply(query) mutates this builder.

Parameters

callback

(scopes) => void

Receives a proxy of the model's named scopes to invoke.

Returns

this

This builder for chaining.

Example

User.query().withScopes((s) => { s.active(); s.byScore(50); }).get();

Select

from()

from(table): this

Defined in: packages/orm/src/db/QueryBuilder.ts:388

Change the table this query targets. Useful for subquery builders that start with an empty table.

Parameters

table

string

Table name; asserted as a safe identifier.

Returns

this

Throws

When table is not a safe SQL identifier.

Inherited from

QueryBuilder.from

select()

select(...columns): this

Defined in: packages/orm/src/db/QueryBuilder.ts:406

Add columns to the SELECT list. Called with no columns the query selects *. Repeated calls accumulate.

Parameters

columns

...string[]

Returns

this

Remarks

Each column is asserted as a safe SELECT entry (a column, table.*, or a simple aggregate, with an optional [AS] alias) — the same identifier guard the where/order builders use. For raw projection SQL, use selectRaw.

Throws

When a column is not a safe select expression.

Inherited from

QueryBuilder.select

distinct()

distinct(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:417

Emit SELECT DISTINCT.

Returns

this

Inherited from

QueryBuilder.distinct

lockForUpdate()

lockForUpdate(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1118

SELECT … FOR UPDATE — take an exclusive row lock. No-op on SQLite, which lacks row locks (the suffix is omitted from the compiled SQL).

Returns

this

Inherited from

QueryBuilder.lockForUpdate

sharedLock()

sharedLock(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1128

Take a shared row lock — LOCK IN SHARE MODE on MySQL, FOR SHARE elsewhere. No-op on SQLite.

Returns

this

Inherited from

QueryBuilder.sharedLock

when()

when(condition, callback): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1248

Conditionally apply builder mutations: when condition is truthy, invoke callback(this, condition) and continue chaining. No otherwise branch.

Parameters

condition

unknown

callback

(q, value) => void

Returns

this

Example

DB.table('users').when(search, (q, s) => q.whereLike('name', `%${s}%`));

Inherited from

QueryBuilder.when

Where clauses

where()

Call Signature

where(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:435

Add an AND WHERE clause. Two-arg form defaults the operator to =; the three-arg form takes an explicit operator (=, !=, >, >=, <, <=, like, not like, in, not in). The column is asserted; the value is always bound.

Parameters

column

string

value

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Example

DB.table('users').where('active', true).where('age', '>=', 18);

Inherited from

QueryBuilder.where

Call Signature

where(column, operator, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:436

Parameters

column

string

operator

WhereOperator

value

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Example

DB.table('users').where('active', true).where('age', '>=', 18);

Inherited from

QueryBuilder.where

Call Signature

where(group): this

Defined in: packages/orm/src/db/QueryBuilder.ts:437

Parameters

group

(query) => void

Returns

this

Throws

When column is not a safe SQL identifier.

Example

DB.table('users').where('active', true).where('age', '>=', 18);

Inherited from

QueryBuilder.where

orWhere()

Call Signature

orWhere(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:459

Add an OR WHERE clause. Same operator/value semantics as where.

Parameters

column

string

value

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhere

Call Signature

orWhere(column, operator, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:460

Add an OR WHERE clause. Same operator/value semantics as where.

Parameters

column

string

operator

WhereOperator

value

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhere

Call Signature

orWhere(group): this

Defined in: packages/orm/src/db/QueryBuilder.ts:461

Add an OR WHERE clause. Same operator/value semantics as where.

Parameters

group

(query) => void

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhere

whereIn()

whereIn(column, values): this

Defined in: packages/orm/src/db/QueryBuilder.ts:485

AND column IN (…). Each value is bound. An empty list compiles to a constant-false predicate (1 = 0) so no rows match.

Parameters

column

string

values

unknown[]

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereIn

orWhereIn()

orWhereIn(column, values): this

Defined in: packages/orm/src/db/QueryBuilder.ts:498

OR column IN (…). See whereIn.

Parameters

column

string

values

unknown[]

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhereIn

whereNotIn()

whereNotIn(column, values): this

Defined in: packages/orm/src/db/QueryBuilder.ts:512

AND column NOT IN (…). An empty list compiles to a constant-true predicate (1 = 1) so all rows match.

Parameters

column

string

values

unknown[]

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereNotIn

orWhereNotIn()

orWhereNotIn(column, values): this

Defined in: packages/orm/src/db/QueryBuilder.ts:525

OR column NOT IN (…). See whereNotIn.

Parameters

column

string

values

unknown[]

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhereNotIn

whereNull()

whereNull(column): this

Defined in: packages/orm/src/db/QueryBuilder.ts:538

AND column IS NULL.

Parameters

column

string

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereNull

orWhereNull()

orWhereNull(column): this

Defined in: packages/orm/src/db/QueryBuilder.ts:550

OR column IS NULL.

Parameters

column

string

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhereNull

whereNotNull()

whereNotNull(column): this

Defined in: packages/orm/src/db/QueryBuilder.ts:562

AND column IS NOT NULL.

Parameters

column

string

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereNotNull

orWhereNotNull()

orWhereNotNull(column): this

Defined in: packages/orm/src/db/QueryBuilder.ts:574

OR column IS NOT NULL.

Parameters

column

string

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhereNotNull

whereBetween()

whereBetween(column, range): this

Defined in: packages/orm/src/db/QueryBuilder.ts:588

AND column BETWEEN ? AND ? — both bounds bound as values.

Parameters

column

string

range

[unknown, unknown]

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereBetween

orWhereBetween()

orWhereBetween(column, range): this

Defined in: packages/orm/src/db/QueryBuilder.ts:597

OR column BETWEEN ? AND ?.

Parameters

column

string

range

[unknown, unknown]

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhereBetween

whereNotBetween()

whereNotBetween(column, range): this

Defined in: packages/orm/src/db/QueryBuilder.ts:606

AND column NOT BETWEEN ? AND ?.

Parameters

column

string

range

[unknown, unknown]

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereNotBetween

orWhereNotBetween()

orWhereNotBetween(column, range): this

Defined in: packages/orm/src/db/QueryBuilder.ts:615

OR column NOT BETWEEN ? AND ?.

Parameters

column

string

range

[unknown, unknown]

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhereNotBetween

whereColumn()

whereColumn(first, operatorOrSecond, second?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:639

Compare two columns instead of a column to a value: whereColumn('updated_at', '>', 'created_at'). With two arguments the operator defaults to =. Both column names are asserted.

Parameters

first

string

operatorOrSecond

string

second?

string

Returns

this

Throws

When either column is unsafe or the operator is unsupported.

Inherited from

QueryBuilder.whereColumn

orWhereColumn()

orWhereColumn(first, operatorOrSecond, second?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:648

OR form of whereColumn.

Parameters

first

string

operatorOrSecond

string

second?

string

Returns

this

Throws

When either column is unsafe or the operator is unsupported.

Inherited from

QueryBuilder.orWhereColumn

whereDate()

whereDate(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:674

Filter on the date portion of a timestamp column. Two-arg form defaults the operator to =. The date-extraction SQL is dialect-specific.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereDate

whereTime()

whereTime(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:682

Filter on the time portion of a timestamp column. See whereDate.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereTime

whereDay()

whereDay(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:690

Filter on the day-of-month component of a timestamp column.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereDay

whereMonth()

whereMonth(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:698

Filter on the month component of a timestamp column.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereMonth

whereYear()

whereYear(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:706

Filter on the year component of a timestamp column.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereYear

whereLike()

whereLike(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:736

AND column LIKE ? — the pattern is bound as a value.

Parameters

column

string

value

string

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereLike

orWhereLike()

orWhereLike(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:746

OR column LIKE ?.

Parameters

column

string

value

string

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhereLike

whereNotLike()

whereNotLike(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:756

AND column NOT LIKE ?.

Parameters

column

string

value

string

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.whereNotLike

orWhereNotLike()

orWhereNotLike(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:766

OR column NOT LIKE ?.

Parameters

column

string

value

string

Returns

this

Throws

When column is not a safe SQL identifier.

Inherited from

QueryBuilder.orWhereNotLike

whereAny()

whereAny(columns, operator, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:779

Match when ANY of the columns satisfies the operator/value, as a parenthesised OR group. Each column is asserted and the value is bound once per column.

Parameters

columns

string[]

operator

string

value

unknown

Returns

this

Throws

When a column is unsafe or the operator is unsupported.

Inherited from

QueryBuilder.whereAny

whereAll()

whereAll(columns, operator, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:788

Match when ALL of the columns satisfy the operator/value, as a parenthesised AND group.

Parameters

columns

string[]

operator

string

value

unknown

Returns

this

Throws

When a column is unsafe or the operator is unsupported.

Inherited from

QueryBuilder.whereAll

whereExists()

whereExists(callback): this

Defined in: packages/orm/src/db/QueryBuilder.ts:824

AND EXISTS (subquery). The callback receives a fresh QueryBuilder to build the correlated subquery; its bindings are merged into the parent.

Parameters

callback

(q) => void

Returns

this

Example

DB.table('users').whereExists((q) =>
  q.from('orders').whereColumn('orders.user_id', 'users.id'));

Inherited from

QueryBuilder.whereExists

orWhereExists()

orWhereExists(callback): this

Defined in: packages/orm/src/db/QueryBuilder.ts:831

OR EXISTS (subquery). See whereExists.

Parameters

callback

(q) => void

Returns

this

Inherited from

QueryBuilder.orWhereExists

whereNotExists()

whereNotExists(callback): this

Defined in: packages/orm/src/db/QueryBuilder.ts:838

AND NOT EXISTS (subquery). See whereExists.

Parameters

callback

(q) => void

Returns

this

Inherited from

QueryBuilder.whereNotExists

orWhereNotExists()

orWhereNotExists(callback): this

Defined in: packages/orm/src/db/QueryBuilder.ts:845

OR NOT EXISTS (subquery). See whereExists.

Parameters

callback

(q) => void

Returns

this

Inherited from

QueryBuilder.orWhereNotExists

whereJson()

whereJson(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1192

Filter by a JSONB/JSON column path value using the ->> text-extraction operator.

Accepts 'column->key' notation. Works natively on PostgreSQL (JSONB columns) and MySQL (JSON columns). For SQLite use whereRaw('json_extract(col, ?) = ?', …).

The column name and key must be safe SQL identifiers (letters, digits, _, .). Throws if either part fails validation — never pass user-controlled strings here.

Parameters

column

string

value

unknown

Returns

this

Throws

When the column or path fails identifier validation.

Example

DB.table('users').whereJson('preferences->theme', 'dark')
// WHERE preferences->>'theme' = ?

Inherited from

QueryBuilder.whereJson