Find Methods

findById()

queryBuilder = queryBuilder.findById(id);

Finds a single item by id.

Arguments

Argument	Type	Description
id	any | any[]	The identifier.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

Examples

const person = await Person.query().findById(1);

Composite key:

const person = await Person.query().findById([1, '10']);

findById can be used together with patch, delete and any other query method. All it does is adds the needed where clauses to the query.

await Person.query()
  .findById(someId)
  .patch({ firstName: 'Jennifer' });

The id condition isn't wrapped in parentheses, so findById(1).orWhere('b', 2) finds the rows where id = 1 or b = 2. Methods that add the id condition themselves, like deleteById(), wrap the other conditions. See Breaking changes.

findByIds()

queryBuilder = queryBuilder.findByIds(ids);

Finds a list of items. The order of the returned items is not guaranteed to be the same as the order of the inputs.

Arguments

Argument	Type	Description
ids	any[]	A List of identifiers.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

Examples

const [person1, person2] = await Person.query().findByIds([1, 2]);

Composite key:

const [person1, person2] = await Person.query().findByIds([
  [1, '10'],
  [2, '10']
]);

findOne()

queryBuilder = queryBuilder.findOne(...whereArgs);

Shorthand for where(...whereArgs).first().

NOTE: .first() doesn't add limit 1 to the query by default. You can override the Model.useLimitInFirst property to change this behaviour.

Arguments

Argument	Type	Description
whereArgs	...any	Anything the where method accepts.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

Examples

const person = await Person.query().findOne({
  firstName: 'Jennifer',
  lastName: 'Lawrence'
});

const person = await Person.query().findOne('age', '>', 20);

const person = await Person.query().findOne(raw('random() < 0.5'));

alias()

queryBuilder = queryBuilder.alias(alias);

Give an alias for the table to be used in the query.

Arguments

Argument	Type	Description
alias	string	Table alias for the query.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

Examples

await Person.query()
  .alias('p')
  .where('p.id', 1)
  .join('persons as parent', 'parent.id', 'p.parentId');

aliasFor()

queryBuilder = queryBuilder.aliasFor(tableNameOrModelClass, alias);

Give an alias for any table in the query.

Arguments

Argument	Type	Description
tableNameOrModelClass	string | ModelClass	The table or model class to alias.
alias	string	The alias.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

Examples

// This query uses joinRelated to join a many-to-many relation which also joins
// the join table `persons_movies`. We specify that the `persons_movies` table
// should be called `pm` instead of the default `movies_join`.
await Person.query()
  .aliasFor('persons_movies', 'pm')
  .joinRelated('movies')
  .where('pm.someProp', 100);

Model class can be used instead of table name

await Person.query()
  .aliasFor(Movie, 'm')
  .joinRelated('movies')
  .where('m.name', 'The Room');

select()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

forUpdate()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

forShare()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

forNoKeyUpdate()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

forKeyShare()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

skipLocked()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

noWait()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

as()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

columns()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

column()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

from()

See knex documentation

The table can also be a model class, or an object like { p: Person } that gives the model's table an alias. See Joins and model classes.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

fromRaw()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

updateFrom()

See knex documentation

Only works on postgres. See knex methods for an example.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

into()

See knex documentation

Takes a table name or a model class, like from.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

with()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

withMaterialized()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

withNotMaterialized()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

withRecursive()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

withWrapped()

The same as with.

withSchema()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

table()

See knex documentation

Takes a table name or a model class, like from.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

distinct()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

distinctOn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

where()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

none()

queryBuilder = queryBuilder.none();

Makes the query match no rows by adding where (1 = 0). Useful in modifiers when there is nothing to match:

function forUsers(query, userIds) {
  if (userIds.length === 0) {
    return query.none();
  }

  query.whereIn('userId', userIds);
}

Return value

Type	Description
QueryBuilder	this query builder for chaining.

andWhere()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhere()

See knex documentation

The conditions objection adds itself, like the owner condition of $relatedQuery() or the id condition of deleteById(), are kept apart from yours with parentheses, so an orWhere() can't match rows outside them. See Breaking changes.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereNot()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereNot()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

andWhereNot()

The same as whereNot.

whereRaw()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereWrapped()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

havingWrapped()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereRaw()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

andWhereRaw()

The same as whereRaw.

whereExists()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereExists()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereNotExists()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereNotExists()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereIn()

See knex documentation

The column can be an expression with bindings, like whereIn(fn.lower(ref('name')), names). The same goes for orWhereIn(), whereNotIn() and orWhereNotIn(). See Joins and model classes.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereIn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereNotIn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereNotIn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereNull()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereNull()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereNotNull()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereNotNull()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereBetween()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereNotBetween()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereBetween()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereNotBetween()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

andWhereBetween()

The same as whereBetween.

andWhereNotBetween()

The same as whereNotBetween.

whereColumn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

andWhereColumn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereColumn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereNotColumn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

andWhereNotColumn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereNotColumn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereLike()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

andWhereLike()

The same as whereLike.

orWhereLike()

See whereLike

whereILike()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

andWhereILike()

The same as whereILike.

orWhereILike()

See whereILike

groupBy()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

groupByRaw()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orderBy()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orderByRaw()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

union()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

unionAll()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

intersect()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

except()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

having()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

havingRaw()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orHaving()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orHavingRaw()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

havingIn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orHavingIn()

See havingIn. Adds the condition with or. In objection 3.1.5 it was added with and. See knex methods.

havingNotIn()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orHavingNotIn()

See havingNotIn.

havingNull()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orHavingNull()

See havingNull.

havingNotNull()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orHavingNotNull()

See havingNotNull.

havingExists()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orHavingExists()

See havingExists.

havingNotExists()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orHavingNotExists()

See havingNotExists.

havingBetween()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orHavingBetween()

See havingBetween. Adds the condition with or. In objection 3.1.5 it was added with and. See knex methods.

havingNotBetween()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orHavingNotBetween()

See havingNotBetween. Adds the condition with or. In objection 3.1.5 it was added with and. See knex methods.

andHaving()

The same as having. There are also andHavingIn(), andHavingNotIn(), andHavingNull(), andHavingNotNull(), andHavingExists(), andHavingNotExists(), andHavingBetween() and andHavingNotBetween(), which are the same as the methods without and. See knex methods.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

clearGroup()

Removes the groupBy and groupByRaw calls of the query.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

clearHaving()

Removes the having calls of the query, including havingIn, orHaving and the others.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

comment()

queryBuilder = queryBuilder.comment('monthly report');

Adds an SQL comment before the query: /* monthly report */ select .... See knex documentation and knex methods.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

hintComment()

queryBuilder = queryBuilder.hintComment(['NO_ICP(persons)']);

Adds optimizer hints to the query: select /*+ NO_ICP(persons) */ .... MySQL and Oracle use the hints. Other databases read them as plain comments. Takes a string or an array of strings. See knex documentation and knex methods.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

offset()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

limit()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

count()

See knex documentation

Also see the resultSize method for a cleaner way to just get the number of rows a query would create.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

countDistinct()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

min()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

max()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

sum()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

sumDistinct()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

avg()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

avgDistinct()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

returning()

See knex documentation

MySQL has no returning(). knex ignores it there with a warning.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

columnInfo()

const info = await Person.query().columnInfo();

See knex documentation

Returns the column info of the model's table, not this query builder. Calls made on the query builder before columnInfo() don't affect it, except withSchema() and debug().

Return value

Type	Description
knex query builder	A separate knex query. Await it to get an object keyed by the column names.

whereComposite()

queryBuilder = queryBuilder.whereComposite(columns, operator, values);

where for (possibly) composite keys.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

Examples

builder.whereComposite(['id', 'name'], '=', [1, 'Jennifer']);

This method also works with a single column - value pair:

builder.whereComposite('id', 1);

whereInComposite()

queryBuilder = queryBuilder.whereInComposite(columns, values);

whereIn for (possibly) composite keys.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

Examples

builder.whereInComposite(
  ['a', 'b'],
  [
    [1, 2],
    [3, 4],
    [1, 4]
  ]
);

builder.whereInComposite('a', [[1], [3], [1]]);

builder.whereInComposite('a', [1, 3, 1]);

builder.whereInComposite(['a', 'b'], SomeModel.query().select('a', 'b'));

whereNotInComposite()

queryBuilder = queryBuilder.whereNotInComposite(columns, values);

whereNotIn for (possibly) composite keys. Takes the same arguments as whereInComposite.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

jsonExtract()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

jsonSet()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

jsonInsert()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

jsonRemove()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

whereJsonObject()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereJsonObject()

See whereJsonObject

andWhereJsonObject()

The same as whereJsonObject.

whereNotJsonObject()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereNotJsonObject()

See whereNotJsonObject

andWhereNotJsonObject()

The same as whereNotJsonObject.

whereJsonPath()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereJsonPath()

See whereJsonPath

andWhereJsonPath()

The same as whereJsonPath.

whereJsonSupersetOf()

queryBuilder = queryBuilder.whereJsonSupersetOf(
  fieldExpression,
  jsonObjectOrFieldExpression
);

Where left hand json field reference is a superset of the right hand json value or reference.

Works on postgres (@>) and MySQL (JSON_CONTAINS()), and throws on other databases. Like on postgres, an object is only contained in an object on MySQL too: whereJsonSupersetOf('arrayColumn', { a: 1 }) doesn't find [{ "a": 1 }]. Below the top level MySQL's rules apply, which also find objects inside arrays. See MySQL.

Arguments

Argument	Type	Description
fieldExpression	FieldExpression	Reference to column / json field, which is tested for being a superset
jsonObjectOrFieldExpression	Object | Array | FieldExpression	To which to compare

Return value

Type	Description
QueryBuilder	this query builder for chaining.

Examples

const people = await Person.query().whereJsonSupersetOf(
  'additionalData:myDogs',
  'additionalData:dogsAtHome'
);

// These people have all or some of their dogs at home. Person might have some
// additional dogs in their custody since myDogs is superset of dogsAtHome.

const people = await Person.query().whereJsonSupersetOf(
  'additionalData:myDogs[0]',
  { name: 'peter' }
);

// These people's first dog name is "peter", but the dog might have
// additional attributes as well.

Object and array are always their own supersets.

For arrays this means that left side matches if it has all the elements listed in the right hand side. e.g.

[1,2,3] isSuperSetOf [2] => true
[1,2,3] isSuperSetOf [2,1,3] => true
[1,2,3] isSuperSetOf [2,null] => false
[1,2,3] isSuperSetOf [] => true

whereJsonNotSupersetOf() and whereJsonNotSubsetOf() don't match rows where a field in the comparison is missing or NULL. For example, for the table

 id |    jsonObject
----+--------------------------
  1 | {}
  2 | NULL
  3 | {"a": 1}
  4 | {"a": 1, "b": 2}
  5 | {"a": ["3"], "b": ["3"]}

this query:

builder.whereJsonNotSupersetOf('jsonObject:a', 'jsonObject:b');

returns only the row 4, which has the keys a and b, and a doesn't contain b. It doesn't return the rows that don't have jsonObject.a or jsonObject.b.

orWhereJsonSupersetOf()

See whereJsonSupersetOf

andWhereJsonSupersetOf()

The same as whereJsonSupersetOf.

whereJsonNotSupersetOf()

See whereJsonSupersetOf

andWhereJsonNotSupersetOf()

The same as whereJsonNotSupersetOf.

orWhereJsonNotSupersetOf()

See whereJsonSupersetOf

whereJsonSubsetOf()

queryBuilder = queryBuilder.whereJsonSubsetOf(
  fieldExpression,
  jsonObjectOrFieldExpression
);

Where left hand json field reference is a subset of the right hand json value or reference.

Object and array are always their own subsets. Works on postgres and MySQL, and throws on other databases.

See whereJsonSupersetOf

Arguments

Argument	Type	Description
fieldExpression	FieldExpression	Reference to column / json field, which is tested for being a subset
jsonObjectOrFieldExpression	Object | Array | FieldExpression	To which to compare

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereJsonSubsetOf()

See whereJsonSubsetOf

andWhereJsonSubsetOf()

The same as whereJsonSubsetOf.

whereJsonNotSubsetOf()

See whereJsonSubsetOf

andWhereJsonNotSubsetOf()

The same as whereJsonNotSubsetOf.

orWhereJsonNotSubsetOf()

See whereJsonSubsetOf

whereJsonIsArray()

queryBuilder = queryBuilder.whereJsonIsArray(fieldExpression);

Where json field reference is an array. Works on postgres and MySQL, and throws on other databases.

Arguments

Argument	Type	Description
fieldExpression	FieldExpression

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereJsonIsArray()

See whereJsonIsArray

whereJsonNotArray()

Where json field reference isn't an array. A NULL or missing field isn't an array, so those rows are found too. See whereJsonIsArray

orWhereJsonNotArray()

See whereJsonIsArray

whereJsonIsObject()

queryBuilder = queryBuilder.whereJsonIsObject(fieldExpression);

Where json field reference is an object. Works on postgres and MySQL, and throws on other databases.

Arguments

Argument	Type	Description
fieldExpression	FieldExpression

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereJsonIsObject()

See whereJsonIsObject

whereJsonNotObject()

Where json field reference isn't an object. A NULL or missing field isn't an object, so those rows are found too. See whereJsonIsObject

orWhereJsonNotObject()

See whereJsonIsObject

whereJsonHasAny()

queryBuilder = queryBuilder.whereJsonHasAny(fieldExpression, keys);

Where any of given strings is found from json object keys, the string items of an array, or a string. Works on postgres and MySQL, and throws on other databases.

TIP

Only the string items of arrays are matched. To check if an array contains an item of another type, use whereJsonSupersetOf with an array: whereJsonSupersetOf('numbers', [1]).

Arguments

Argument	Type	Description
fieldExpression	FieldExpression
keys	string | string[]	Strings that are looked from object or array

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereJsonHasAny()

See whereJsonHasAny

whereJsonHasAll()

queryBuilder = queryBuilder.whereJsonHasAll(fieldExpression, keys);

Where all of given strings are found from json object keys, the string items of an array, or a string. Works on postgres and MySQL, and throws on other databases.

TIP

Only the string items of arrays are matched. To check if an array contains items of another type, use whereJsonSupersetOf with an array: whereJsonSupersetOf('numbers', [1, 2]).

Arguments

Argument	Type	Description
fieldExpression	FieldExpression
keys	string | string[]	Strings that are looked from object or array

Return value

Type	Description
QueryBuilder	this query builder for chaining.

orWhereJsonHasAll()

See whereJsonHasAll

whereJsonOverlaps()

queryBuilder = queryBuilder.whereJsonOverlaps(
  fieldExpression,
  jsonObjectOrFieldExpression
);

MySQL only. Where the json field and the json value or reference overlap: two arrays share an item, two objects share a key and value, or an array contains a scalar. Uses JSON_OVERLAPS(), which can use multi-valued indexes. On postgres it throws whereJsonOverlaps is only supported on mysql., and on MariaDB it needs 10.9 or newer. See MySQL.

Arguments

Argument	Type	Description
fieldExpression	FieldExpression	Reference to a field
jsonObjectOrFieldExpression	Object | Array | FieldExpression	To which to compare

Return value

Type	Description
QueryBuilder	this query builder for chaining.

Examples

const jobs = await Job.query().whereJsonOverlaps('attributes:countryCodes', ['FI', 'SE']);

orWhereJsonOverlaps()

See whereJsonOverlaps

andWhereJsonOverlaps()

The same as whereJsonOverlaps.

whereJsonNotOverlaps()

MySQL only. The negation of whereJsonOverlaps. A NULL or missing field doesn't overlap anything, so rows without the field are found.

// Jobs that aren't restricted to any of the countries.
const jobs = await Job.query().whereJsonNotOverlaps('attributes:countryCodes', ['FI', 'SE']);

andWhereJsonNotOverlaps()

The same as whereJsonNotOverlaps.

orWhereJsonNotOverlaps()

See whereJsonNotOverlaps