Relations, three level nested where?

Given a User, Role, RoleToUser many-to-many relation:

export const userRelations = relations(User, ({ many }) => ({
  roleToUser: many(RoleToUser),
}));

export const roleRelations = relations(Role, ({ many }) => ({
  roleToUser: many(RoleToUser),
}));

export const roleToUserRelations = relations(RoleToUser, ({ one }) => ({
  role: one(Role, { fields: [RoleToUser.roleId], references: [Role.id] }),
  user: one(User, { fields: [RoleToUser.userId], references: [User.id] }),
}));

export const userRelations = relations(User, ({ many }) => ({
  roleToUser: many(RoleToUser),
}));

export const roleRelations = relations(Role, ({ many }) => ({
  roleToUser: many(RoleToUser),
}));

export const roleToUserRelations = relations(RoleToUser, ({ one }) => ({
  role: one(Role, { fields: [RoleToUser.roleId], references: [Role.id] }),
  user: one(User, { fields: [RoleToUser.userId], references: [User.id] }),
}));

Are we able to filter with where on the third level to, for example, find all users belonging to a role of the given name?

db.query.User.findMany({
  with: {
    roleToUser: {
      with: {
        role: {
          where: eq(Role.name, "現場監督"),
        },
      },
    },
  },
}),

db.query.User.findMany({
  with: {
    roleToUser: {
      with: {
        role: {
          where: eq(Role.name, "現場監督"),
        },
      },
    },
  },
}),

Currently it doesn't seem to work, although of course in this example I can start with Role instead to achieve the same thing....

5 Replies

Dan•2y ago

Currently, you can filter the tables on their own fields only. Filtering on deeply nested fields is not implemented - https://github.com/drizzle-team/drizzle-orm/issues/696

GitHub

Allow referencing deeply nested properties in relational queries · ...

panchocorderos•2y ago

Curious if this example in the documentation may be wrong, or we re doing something wrong on our end? https://orm.drizzle.team/docs/rqb#include-relations

Relational queries – DrizzleORM

Drizzle ORM | %s

Mendy•17mo ago

Yes, AFAIK this is deprecated since last version.

Dan•17mo ago

no, that example looks correct the only thing we deprecated is filtering by nested relations from the outer query so on every relation level, you can filter by that relation's columns

MAST•17mo ago

Hmm, interesting I have a similar issue as well. https://discord.com/channels/1043890932593987624/1139100297898238062 The orderBy'a function parameters are type any and there's no where to filter as well.

Gaming

Programming

Relations, three level nested where?