multiple joins of same table in one query?

i want to do this query

return await db
  .select({
    ...getTableColumns(interests),
    user: getTableColumns(users),
    referral: getTableColumns(referrals),
    referrer: getTableColumns(users),
  })
  .from(interests)
  .innerJoin(users, eq(interests.userId, users.id))
  .leftJoin(referrals, eq(interests.referralId, referrals.id))
  .leftJoin(users, eq(referrals.referrer, users.id));

return await db
  .select({
    ...getTableColumns(interests),
    user: getTableColumns(users),
    referral: getTableColumns(referrals),
    referrer: getTableColumns(users),
  })
  .from(interests)
  .innerJoin(users, eq(interests.userId, users.id))
  .leftJoin(referrals, eq(interests.referralId, referrals.id))
  .leftJoin(users, eq(referrals.referrer, users.id));

basically i have one inner join on users and also a left join on users. but the resulting type makes all fields never when i remove either of the joins on users, i get the correct types

2 Replies

Angelelz•12mo ago

When you're doing self joins you need to create aliases

Angelelz•12mo ago

https://orm.drizzle.team/docs/joins#aliases--selfjoins

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

multiple joins of same table in one query?