Find Methods

findById()

queryBuilder = queryBuilder.findById(id);

Finds a single item by id.

Arguments

Return value

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

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

Return value

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

Return value

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

Return value

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

Return value

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

forUpdate()

See knex documentation

Return value

forShare()

See knex documentation

Return value

forNoKeyUpdate()

See knex documentation

Return value

forKeyShare()

See knex documentation

Return value

skipLocked()

See knex documentation

Return value

noWait()

See knex documentation

Return value

as()

See knex documentation

Return value

columns()

See knex documentation

Return value

column()

See knex documentation

Return value

from()

See knex documentation

Return value

fromRaw()

See knex documentation

Return value

updateFrom()

See knex documentation

Return value

into()

See knex documentation

Return value

with()

See knex documentation

Return value

withMaterialized()

See knex documentation

Return value

withNotMaterialized()

See knex documentation

Return value

withSchema()

See knex documentation

Return value

table()

See knex documentation

Return value

distinct()

See knex documentation

Return value

distinctOn()

See knex documentation

Return value

where()

See knex documentation

Return value

andWhere()

See knex documentation

Return value

orWhere()

See knex documentation

Return value

whereNot()

See knex documentation

Return value

orWhereNot()

See knex documentation

Return value

whereRaw()

See knex documentation

Return value

whereWrapped()

See knex documentation

Return value

havingWrapped()

See knex documentation

Return value

orWhereRaw()

See knex documentation

Return value

whereExists()

See knex documentation

Return value

orWhereExists()

See knex documentation

Return value

whereNotExists()

See knex documentation

Return value

orWhereNotExists()

See knex documentation

Return value

whereIn()

See knex documentation

Return value

orWhereIn()

See knex documentation

Return value

whereNotIn()

See knex documentation

Return value

orWhereNotIn()

See knex documentation

Return value

whereNull()

See knex documentation

Return value

orWhereNull()

See knex documentation

Return value

whereNotNull()

See knex documentation

Return value

orWhereNotNull()

See knex documentation

Return value

whereBetween()

See knex documentation

Return value

whereNotBetween()

See knex documentation

Return value

orWhereBetween()

See knex documentation

Return value

orWhereNotBetween()

See knex documentation

Return value

whereColumn()

See knex documentation

Return value

andWhereColumn()

See knex documentation

Return value

orWhereColumn()

See knex documentation

Return value

whereNotColumn()

See knex documentation

Return value

andWhereNotColumn()

See knex documentation

Return value

orWhereNotColumn()

See knex documentation

Return value

whereLike()

See knex documentation

Return value

whereILike()

See knex documentation

Return value

groupBy()

See knex documentation

Return value

groupByRaw()

See knex documentation

Return value

orderBy()

See knex documentation

Return value

orderByRaw()

See knex documentation

Return value

union()

See knex documentation

Return value

unionAll()

See knex documentation

Return value

intersect()

See knex documentation

Return value

having()

See knex documentation

Return value

havingRaw()

See knex documentation

Return value

orHaving()

See knex documentation

Return value

orHavingRaw()

See knex documentation

Return value

offset()

See knex documentation

Return value

limit()

See knex documentation

Return value

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

countDistinct()

See knex documentation

Return value

min()

See knex documentation

Return value

max()

See knex documentation

Return value

sum()

See knex documentation

Return value

avg()

See knex documentation

Return value

avgDistinct()

See knex documentation

Return value

returning()

See knex documentation

Return value

columnInfo()

See knex documentation

Return value

whereComposite()

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

where for (possibly) composite keys.

Return value

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

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'));

jsonExtract()

See knex documentation

Return value

jsonSet()

See knex documentation

Return value

jsonInsert()

See knex documentation

Return value

jsonRemove()

See knex documentation

Return value

whereJsonObject()

See knex documentation

Return value

whereNotJsonObject()

See knex documentation

Return value

whereJsonPath()

See knex documentation

Return value

whereJsonSupersetOf()

queryBuilder = queryBuilder.whereJsonSupersetOf(
  fieldExpression,
  jsonObjectOrFieldExpression
);

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

Arguments

Return value

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

The not variants with jsonb operators behave in a way that they won't match rows, which don't have the referred json key referred in field expression. e.g. for table

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

this query:

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

Returns only the row 4 which has keys a and b and a != b, but it won't return any rows that don't have jsonObject.a or jsonObject.b.

orWhereJsonSupersetOf()

See whereJsonSupersetOf

whereJsonNotSupersetOf()

See whereJsonSupersetOf

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.

See whereJsonSupersetOf

Arguments

Return value

orWhereJsonSubsetOf()

See whereJsonSubsetOf

whereJsonNotSubsetOf()

See whereJsonSubsetOf

orWhereJsonNotSubsetOf()

See whereJsonSubsetOf

whereJsonIsArray()

queryBuilder = queryBuilder.whereJsonIsArray(fieldExpression);

Where json field reference is an array.

Arguments

Return value

orWhereJsonIsArray()

See whereJsonIsArray

whereJsonNotArray()

See whereJsonIsArray

orWhereJsonNotArray()

See whereJsonIsArray

whereJsonIsObject()

queryBuilder = queryBuilder.whereJsonIsObject(fieldExpression);

Where json field reference is an object.

Arguments

Return value

orWhereJsonIsObject()

See whereJsonIsObject

whereJsonNotObject()

See whereJsonIsObject

orWhereJsonNotObject()

See whereJsonIsObject

whereJsonHasAny()

queryBuilder = queryBuilder.whereJsonHasAny(fieldExpression, keys);

Where any of given strings is found from json object keys.

::: tip This doesn't work for arrays. If you want to check if an array contains an item, see this and this issue. :::

Arguments

Return value

orWhereJsonHasAny()

See whereJsonHasAny

whereJsonHasAll()

queryBuilder = queryBuilder.whereJsonHasAll(fieldExpression, keys);

Where all of given strings are found from json object keys.

::: tip This doesn't work for arrays. If you want to check if an array contains an item, see this and this issue. :::

Arguments

Return value

orWhereJsonHasAll()

See whereJsonHasAll