Can we use drizzle to query using related fields?

Question

For example, I have three tables as follows:

User:

• id
• name
• email

Organisation:

• id
• name

OrganisationToUser:

• userId
• organisationIsd

One user has many organisations and one organisation have many users. This is a Planetscale database so no foreign keys. Only virtual relationships. In this case, how can I filter users with their email and organisationId? Here's something I tried:

const userData = await db.query.user.findFirst({
    with: {
      organisationToUsers: {
        with: {
          organisation: true,
        },
        where: eq(organisationToUser.a, 'wrongOrganisationId'),
      },
    },
    where: eq(user.email, ctx.session?.user.email || ''),
  });

Unfortunately, this does not work. It only filters the user's existing organisations but still return the user. Any idea how can I get this done?

Answer 1

I spent years with Prisma and forgot to check on this. For anyone who is coming from an ORM like Prisma, Drizzle resembles SQL closely. So to do the above function, the SQL will be:

SELECT user.id, user.email
FROM User user
INNER JOIN _OrganisationToUser organisation ON user.id = organisation.B 
WHERE user.email = '[email protected]' AND organisation.A  = 'organisationId';

In drizzle, this will look like:

const user = await db
  .select()
  .from(user)
  .innerJoin(organisationToUser, eq(organisationToUser.b, user.id))
  .where(
    and(
      eq(user.email, '[email protected]'),
      eq(organisationToUser.a, 'organisationId'),
    ),
  );