# 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.

# 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.

# 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.

# 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.

# 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.

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

# 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()

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
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()

See whereJsonSubsetOf

# whereJsonNotSubsetOf()

See whereJsonSubsetOf

# orWhereJsonNotSubsetOf()

See whereJsonSubsetOf

# 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()

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 (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