Join Methods

joinRelated()

queryBuilder = queryBuilder.joinRelated(relationExpression, opt);

Joins a set of relations described by relationExpression. See the examples for more info.

Arguments

Argument	Type	Description
relationExpression	RelationExpression	An expression describing which relations to join.
opt	object	Optional options. See the examples.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

Examples

Join one relation:

await Person.query()
  .joinRelated('pets')
  .where('pets.species', 'dog');

Give an alias for a single relation:

await Person.query()
  .joinRelated('pets', { alias: 'p' })
  .where('p.species', 'dog');

Join two relations:

await Person.query()
  .joinRelated('[pets, parent]')
  .where('pets.species', 'dog')
  .where('parent.name', 'Arnold');

You can also use the object notation

await Person.query()
  .joinRelated({
    pets: true,
    parent: true
  })
  .where('pets.species', 'dog')
  .where('parent.name', 'Arnold');

Join multiple nested relations. Note that when referring to nested relations : must be used as a separator instead of .. This limitation comes from the way knex parses table references.

await Person.query()
  .select('persons.id', 'parent:parent.name as grandParentName')
  .joinRelated('[pets, parent.[pets, parent]]')
  .where('parent:pets.species', 'dog');

Give aliases for a bunch of relations:

await Person.query()
  .select('persons.id', 'pr:pr.name as grandParentName')
  .joinRelated('[pets, parent.[pets, parent]]', {
    aliases: {
      parent: 'pr',
      pets: 'pt'
    }
  })
  .where('pr:pt.species', 'dog');

You can also give aliases using the relation expression:

await Person.query()
  .select('persons.id', 'pr:pr.name as grandParentName')
  .joinRelated('[pets as pt, parent as pr.[pets as pt, parent as pr]]')
  .where('pr:pt.species', 'dog');

innerJoinRelated()

Alias for joinRelated.

outerJoinRelated()

Outer join version of the joinRelated method.

It builds a bare outer join, which postgres, MySQL and SQLite reject. Use leftOuterJoinRelated, or fullOuterJoinRelated on postgres and SQLite.

leftJoinRelated()

Left join version of the joinRelated method.

leftOuterJoinRelated()

Left outer join version of the joinRelated method.

rightJoinRelated()

Right join version of the joinRelated method.

rightOuterJoinRelated()

Right outer join version of the joinRelated method.

fullOuterJoinRelated()

Full outer join version of the joinRelated method. MySQL doesn't support full outer joins.

join()

See knex documentation

The table can be a table name, a model class, an object like { parent: Person } that gives the table an alias, or a subquery named with as(). See Joins and model classes.

await Person.query()
  .innerJoin(Pet, Pet.ref('ownerId'), Person.ref('id'))
  .leftJoin({ parent: Person }, 'parent.id', Person.ref('parentId'));

A join callback gets a join builder with knex's on* methods: on(), andOn(), orOn(), onIn(), onNull(), onExists(), onBetween(), their and, or and not variants, onVal(), andOnVal(), orOnVal(), onJsonPathEquals(), andOnJsonPathEquals(), orOnJsonPathEquals(), using() and type(). ref(), Model.ref() and the expressions work in them, and join.on(a, b).or.on(c, d) is the same as join.on(a, b).orOn(c, d).

await Person.query().join(Pet, (join) =>
  join
    .on(Pet.ref('ownerId'), Person.ref('id'))
    .andOnVal(Pet.ref('species'), 'dog'),
);
// select "persons".* from "persons"
// inner join "pets" on "pets"."ownerId" = "persons"."id" and "pets"."species" = 'dog'

See Model.ref() in join callbacks and onJsonPathEquals() in join callbacks.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

joinRaw()

See knex documentation

Return value

Type	Description
QueryBuilder	this query builder for chaining.

innerJoin()

See knex documentation

Takes the same arguments as join.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

leftJoin()

See knex documentation

Takes the same arguments as join.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

leftOuterJoin()

See knex documentation

Takes the same arguments as join.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

rightJoin()

See knex documentation

Takes the same arguments as join.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

rightOuterJoin()

See knex documentation

Takes the same arguments as join.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

outerJoin()

Takes the same arguments as join.

knex builds a bare outer join, which postgres, MySQL and SQLite reject. Use leftOuterJoin, or fullOuterJoin on postgres and SQLite.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

fullOuterJoin()

See knex documentation

Takes the same arguments as join. MySQL doesn't support full outer joins.

Return value

Type	Description
QueryBuilder	this query builder for chaining.

crossJoin()

See knex documentation

Takes the same arguments as join.

Return value

Type	Description
QueryBuilder	this query builder for chaining.