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

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

none()

queryBuilder = queryBuilder.none();

Makes the query return no results without executing it. Find queries return an empty array (or undefined if first, findById or findOne is used), and no withGraphFetched queries are executed either. Update, patch and delete queries return 0 (or an empty array if returning is used). Using none() with insert, relate and unrelate queries throws an error. Query hooks are still called, and the after hooks receive the empty result.

none() replaces all where clauses of the query with an always false condition (where 1 = 0), so that the query matches no rows when it is executed anyway. This is the case when the query is used as a subquery or in resultSize, and for find queries that use aggregate methods like count or groupBy. These are executed so that they return what the database returns for no rows, for example [{ count: 0 }].

This is useful when building queries in functions that are chained further by the caller, but one of them decides that the query should not return anything.

Return value

Type	Description
QueryBuilder	`this` query builder for chaining.

Examples

function findVisibleMovies(user) {
  const query = Movie.query().orderBy('name');
  return user.canSeeMovies ? query : query.none();
}

// Returns an empty array without executing any queries
// if the user can't see movies.
const movies = await findVisibleMovies(user).withGraphFetched('actors');

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

Find Methods ​

findById() ​

Arguments ​

Return value ​

Examples ​

findByIds() ​

Arguments ​

Return value ​

Examples ​

findOne() ​

Arguments ​

Return value ​

Examples ​

none() ​

Return value ​

Examples ​

alias() ​

Arguments ​

Return value ​

Examples ​

aliasFor() ​

Arguments ​

Return value ​

Examples ​

select() ​

Return value ​

forUpdate() ​

Return value ​

forShare() ​

Return value ​

forNoKeyUpdate() ​

Return value ​

forKeyShare() ​

Return value ​

skipLocked() ​

Return value ​

noWait() ​

Return value ​

as() ​

Return value ​

columns() ​

Return value ​

column() ​

Return value ​

from() ​

Return value ​

fromRaw() ​

Return value ​

updateFrom() ​

Return value ​

into() ​

Return value ​

with() ​

Return value ​

withMaterialized() ​

Return value ​

withNotMaterialized() ​

Return value ​

withSchema() ​

Return value ​

table() ​

Return value ​

distinct() ​

Return value ​

distinctOn() ​

Return value ​

where() ​

Return value ​

andWhere() ​

Return value ​

orWhere() ​

Return value ​

whereNot() ​

Return value ​

orWhereNot() ​

Return value ​

whereRaw() ​

Return value ​

whereWrapped() ​

Return value ​

Find Methods

findById()

Arguments

Return value

Examples

findByIds()

Arguments

Return value

Examples

findOne()

Arguments

Return value

Examples

none()

Return value

Examples

alias()

Arguments

Return value

Examples

aliasFor()

Arguments

Return value

Examples

select()

Return value

forUpdate()

Return value

forShare()

Return value

forNoKeyUpdate()

Return value

forKeyShare()

Return value

skipLocked()

Return value

noWait()

Return value

as()

Return value

columns()

Return value

column()

Return value

from()

Return value

fromRaw()

Return value

updateFrom()

Return value

into()

Return value

with()

Return value

withMaterialized()

Return value

withNotMaterialized()

Return value

withSchema()

Return value

table()

Return value

distinct()

Return value

distinctOn()

Return value

where()

Return value

andWhere()

Return value

orWhere()

Return value

whereNot()

Return value

orWhereNot()

Return value

whereRaw()

Return value

whereWrapped()

Return value