innerJoin

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

const users = await User.innerJoin('pets', 'collars').all()

select "users".* from "users"
inner join "pets" on "users"."id" = "pets"."user_id"
inner join "collars" on "pets"."id" = "collars"."pet_id"

You can also attach where clauses to join statements. The second argument (the object) in the following code generates a where clause on the posts model:

const users = await User.innerJoin('pets', { on: { name: 'Aster' } }).all()

select "users".* from "users"
inner join "pets" on "users"."id" = "pets"."user_id" and "pets"."name" = $1

// A where statement on both `posts` and `comments`
const users = await User.innerJoin(
  'posts',
  { on: { category: 'travel' } },
  'comments',
  { on: flagged: true },
).all()

// A where statement on just `comments`
const users = await User.innerJoin('posts', 'comments', { on: { flagged: true } }).all()