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 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 users = await User.innerJoin('pets', { and: { name: 'Aster' } }).all()

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

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

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