# 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 withpatch,delete and any other query method. All it does is adds the neededwhere 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 forwhere(...whereArgs).first().

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

Seeknex documentation(opens new window)

# Return value

# forUpdate()

Seeknex documentation(opens new window)

# Return value

# forShare()

Seeknex documentation(opens new window)

# Return value

# forNoKeyUpdate()

Seeknex documentation(opens new window)

# Return value

# forKeyShare()

Seeknex documentation(opens new window)

# Return value

# skipLocked()

Seeknex documentation(opens new window)

# Return value

# noWait()

Seeknex documentation(opens new window)

# Return value

# as()

Seeknex documentation(opens new window)

# Return value

# columns()

Seeknex documentation(opens new window)

# Return value

# column()

Seeknex documentation(opens new window)

# Return value

# from()

Seeknex documentation(opens new window)

# Return value

# fromRaw()

Seeknex documentation(opens new window)

# Return value

# into()

Seeknex documentation(opens new window)

# Return value

# with()

Seeknex documentation(opens new window)

# Return value

# withMaterialized()

Seeknex documentation(opens new window)

# Return value

# withNotMaterialized()

Seeknex documentation(opens new window)

# Return value

# withSchema()

Seeknex documentation(opens new window)

# Return value

# table()

Seeknex documentation(opens new window)

# Return value

# distinct()

Seeknex documentation(opens new window)

# Return value

# distinctOn()

Seeknex documentation(opens new window)

# Return value

# where()

Seeknex documentation(opens new window)

# Return value

# andWhere()

Seeknex documentation(opens new window)

# Return value

# orWhere()

Seeknex documentation(opens new window)

# Return value

# whereNot()

Seeknex documentation(opens new window)

# Return value

# orWhereNot()

Seeknex documentation(opens new window)

# Return value

# whereRaw()

Seeknex documentation(opens new window)

# Return value

# whereWrapped()

Seeknex documentation(opens new window)

# Return value

# havingWrapped()

Seeknex documentation(opens new window)

# Return value

# orWhereRaw()

Seeknex documentation(opens new window)

# Return value

# whereExists()

Seeknex documentation(opens new window)

# Return value

# orWhereExists()

Seeknex documentation(opens new window)

# Return value

# whereNotExists()

Seeknex documentation(opens new window)

# Return value

# orWhereNotExists()

Seeknex documentation(opens new window)

# Return value

# whereIn()

Seeknex documentation(opens new window)

# Return value

# orWhereIn()

Seeknex documentation(opens new window)

# Return value

# whereNotIn()

Seeknex documentation(opens new window)

# Return value

# orWhereNotIn()

Seeknex documentation(opens new window)

# Return value

# whereNull()

Seeknex documentation(opens new window)

# Return value

# orWhereNull()

Seeknex documentation(opens new window)

# Return value

# whereNotNull()

Seeknex documentation(opens new window)

# Return value

# orWhereNotNull()

Seeknex documentation(opens new window)

# Return value

# whereBetween()

Seeknex documentation(opens new window)

# Return value

# whereNotBetween()

Seeknex documentation(opens new window)

# Return value

# orWhereBetween()

Seeknex documentation(opens new window)

# Return value

# orWhereNotBetween()

Seeknex documentation(opens new window)

# Return value

# whereColumn()

Seeknex documentation(opens new window)

# Return value

# andWhereColumn()

Seeknex documentation(opens new window)

# Return value

# orWhereColumn()

Seeknex documentation(opens new window)

# Return value

# whereNotColumn()

Seeknex documentation(opens new window)

# Return value

# andWhereNotColumn()

Seeknex documentation(opens new window)

# Return value

# orWhereNotColumn()

Seeknex documentation(opens new window)

# Return value

# whereLike()

Seeknex documentation(opens new window)

# Return value

# whereILike()

Seeknex documentation(opens new window)

# Return value

# groupBy()

Seeknex documentation(opens new window)

# Return value

# groupByRaw()

Seeknex documentation(opens new window)

# Return value

# orderBy()

Seeknex documentation(opens new window)

# Return value

# orderByRaw()

Seeknex documentation(opens new window)

# Return value

# union()

Seeknex documentation(opens new window)

# Return value

# unionAll()

Seeknex documentation(opens new window)

# Return value

# intersect()

Seeknex documentation(opens new window)

# Return value

# having()

Seeknex documentation(opens new window)

# Return value

# havingRaw()

Seeknex documentation(opens new window)

# Return value

# orHaving()

Seeknex documentation(opens new window)

# Return value

# orHavingRaw()

Seeknex documentation(opens new window)

# Return value

# offset()

Seeknex documentation(opens new window)

# Return value

# limit()

Seeknex documentation(opens new window)

# Return value

# count()

Seeknex documentation(opens new window)

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

# Return value

# countDistinct()

Seeknex documentation(opens new window)

# Return value

# min()

Seeknex documentation(opens new window)

# Return value

# max()

Seeknex documentation(opens new window)

# Return value

# sum()

Seeknex documentation(opens new window)

# Return value

# avg()

Seeknex documentation(opens new window)

# Return value

# avgDistinct()

Seeknex documentation(opens new window)

# Return value

# returning()

Seeknex documentation(opens new window)

# Return value

# columnInfo()

Seeknex documentation(opens new window)

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

Seeknex documentation(opens new window)

# Return value

# jsonSet()

Seeknex documentation(opens new window)

# Return value

# jsonInsert()

Seeknex documentation(opens new window)

# Return value

# jsonRemove()

Seeknex documentation(opens new window)

# Return value

# whereJsonObject()

Seeknex documentation(opens new window)

# Return value

# whereNotJsonObject()

Seeknex documentation(opens new window)

# Return value

# whereJsonPath()

Seeknex documentation(opens new window)

# 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

Thenot 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 row4 which has keysa andb anda !=b, but it won't return any rows that don't havejsonObject.a orjsonObject.b.

# orWhereJsonSupersetOf()

SeewhereJsonSupersetOf

# whereJsonNotSupersetOf()

SeewhereJsonSupersetOf

# orWhereJsonNotSupersetOf()

SeewhereJsonSupersetOf

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

SeewhereJsonSupersetOf

# Arguments

# Return value

# orWhereJsonSubsetOf()

SeewhereJsonSubsetOf

# whereJsonNotSubsetOf()

SeewhereJsonSubsetOf

# orWhereJsonNotSubsetOf()

SeewhereJsonSubsetOf

# whereJsonIsArray()

queryBuilder= queryBuilder.whereJsonIsArray(fieldExpression);

Where json field reference is an array.

# Arguments

# Return value

# orWhereJsonIsArray()

SeewhereJsonIsArray

# whereJsonNotArray()

SeewhereJsonIsArray

# orWhereJsonNotArray()

SeewhereJsonIsArray

# whereJsonIsObject()

queryBuilder= queryBuilder.whereJsonIsObject(fieldExpression);

Where json field reference is an object.

# Arguments

# Return value

# orWhereJsonIsObject()

SeewhereJsonIsObject

# whereJsonNotObject()

SeewhereJsonIsObject

# orWhereJsonNotObject()

SeewhereJsonIsObject

# whereJsonHasAny()

queryBuilder= queryBuilder.whereJsonHasAny(fieldExpression, keys);

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

# Arguments

# Return value

# orWhereJsonHasAny()

SeewhereJsonHasAny

# whereJsonHasAll()

queryBuilder= queryBuilder.whereJsonHasAll(fieldExpression, keys);

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

# Arguments

# Return value

# orWhereJsonHasAll()

SeewhereJsonHasAll

Mutating Methods →