Drizzle Team•2y ago

many-many with

Hi. I have a schema with users, roles and user_to_roles tables. I also have a usersRelations object, created with:

export const usersRelations = relations(users, ({ one, many }) => ({
  usersToRoles: many(usersToRoles),
  ...

export const usersRelations = relations(users, ({ one, many }) => ({
  usersToRoles: many(usersToRoles),
  ...

Is there a way to use with in a relational query to get back a user object (or an array of user objects) with a roles property containing an array of all roles for the user?

1 Reply

groovebucketOP•2y ago

I know that I can do:

db
    .select()
    .from(users)
    .leftJoin(usersToRoles, eq(usersToRoles.userId, users.id))
    .leftJoin(roles, eq(roles.id, usersToRoles.roleId))
    .where(eq(users.username, "admin"));

db
    .select()
    .from(users)
    .leftJoin(usersToRoles, eq(usersToRoles.userId, users.id))
    .leftJoin(roles, eq(roles.id, usersToRoles.roleId))
    .where(eq(users.username, "admin"));

and massage the result into what I want. But, I'm wondering whether there might be a way to get there using relational queries.

Gaming

Programming

many-many with