# 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' });
# 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().
# 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 (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# forUpdate()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# forShare()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# forNoKeyUpdate()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# forKeyShare()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# skipLocked()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# noWait()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# as()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# columns()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# column()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# from()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# fromRaw()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# into()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# with()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# withMaterialized()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# withNotMaterialized()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# withSchema()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# table()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# distinct()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# distinctOn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# where()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# andWhere()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhere()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereNot()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereNot()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereRaw()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereWrapped()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# havingWrapped()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereRaw()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereExists()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereExists()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereNotExists()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereNotExists()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereIn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereIn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereNotIn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereNotIn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereNull()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereNull()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereNotNull()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereNotNull()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereBetween()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereNotBetween()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereBetween()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereNotBetween()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereColumn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# andWhereColumn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereColumn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereNotColumn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# andWhereNotColumn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereNotColumn()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereLike()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereILike()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# groupBy()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# groupByRaw()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orderBy()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orderByRaw()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# union()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# unionAll()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# intersect()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# having()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# havingRaw()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orHaving()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orHavingRaw()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# offset()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# limit()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# count()
See knex documentation (opens new window)
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 (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# min()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# max()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# sum()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# avg()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# avgDistinct()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# returning()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# columnInfo()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# 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'));
# jsonExtract()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# jsonSet()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# jsonInsert()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# jsonRemove()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereJsonObject()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereNotJsonObject()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereJsonPath()
See knex documentation (opens new window)
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# whereJsonSupersetOf()
queryBuilder = queryBuilder.whereJsonSupersetOf(
fieldExpression,
jsonObjectOrFieldExpression
);
Where left hand json field reference is a superset of the right hand json value or reference.
# 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
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()
# whereJsonNotSupersetOf()
# orWhereJsonNotSupersetOf()
# 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.
# 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. |
# orWhereJsonSubsetOf()
# whereJsonNotSubsetOf()
# orWhereJsonNotSubsetOf()
# whereJsonIsArray()
queryBuilder = queryBuilder.whereJsonIsArray(fieldExpression);
Where json field reference is an array.
# Arguments
| Argument | Type | Description |
|---|---|---|
| fieldExpression | FieldExpression |
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereJsonIsArray()
See whereJsonIsArray
# whereJsonNotArray()
See whereJsonIsArray
# orWhereJsonNotArray()
See whereJsonIsArray
# whereJsonIsObject()
queryBuilder = queryBuilder.whereJsonIsObject(fieldExpression);
Where json field reference is an object.
# Arguments
| Argument | Type | Description |
|---|---|---|
| fieldExpression | FieldExpression |
# Return value
| Type | Description |
|---|---|
| QueryBuilder | this query builder for chaining. |
# orWhereJsonIsObject()
# whereJsonNotObject()
# orWhereJsonNotObject()
# 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 (opens new window) and this (opens new window) issue.
# 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.
TIP
This doesn't work for arrays. If you want to check if an array contains an item, see this (opens new window) and this (opens new window) issue.
# 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