innerJoin

The innerJoin method enables you to join associations as part of your queries:

const hosts = await Host.innerJoin('places', 'rooms').all()

select "hosts".* from "hosts"
inner join "places" on "hosts"."id" = "places"."host_id"
inner join "rooms" on "places"."id" = "rooms"."place_id"

tip

Multiple string arguments describe a single traversal path (an association chain) through your associations, not parallel joins — see Association chaining for the full concept.

Join conditions

You can also attach and, andNot, and andAny clauses to join statements. The second argument (the object) in the following code generates an and condition on the join:

const hosts = await Host.innerJoin('places', {
  and: { name: 'Mountain Cabin' },
}).all()

select "hosts".* from "hosts"
inner join "places" on "hosts"."id" = "places"."host_id" and "places"."name" = $1

// An `and` statement on both `places` and `rooms`
const hosts = await Host.innerJoin(
  'places',
  { and: { style: 'cabin' } },
  'rooms',
  { and: { type: 'Bedroom' } }
).all()

// An `and` statement on just `rooms`
const hosts = await Host.innerJoin('places', 'rooms', {
  and: { type: 'Bedroom' },
}).all()

andNot and andAny follow the same whereNot / whereAny semantics, respectively, and can appear anywhere and can:

// andNot: negate a condition on the join
const hosts = await Host.innerJoin('places', { andNot: { style: 'dump' } }).all()

// andAny: OR several conditions on the join (keys within one object are AND'd)
const hosts = await Host.innerJoin('places', {
  andAny: [{ style: 'cabin' }, { style: 'treehouse' }],
}).all()