Relations, three level nested where?

Ttacomanator5/26/2023
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....
Bbloberenober6/2/2023
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
Ppanchocorderos8/10/2023
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
MMendy8/10/2023
Yes, AFAIK this is deprecated since last version.
Bbloberenober8/10/2023
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
MMAST8/10/2023
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.

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
Help with this relational query?I am trying to get all the organizations that a member is associated with. This code is working: RQB | using specific fields from a query against relationsis there a way to create a select filter that matches specific fields from a relation? say i `findMaSimulate enums with SQLite `CHECK()`I'm a heavy enum user in Postgres. Using SQLite now, wondering if anyone has come up with something Migrating from Prisma graduallyWe're in the middle of migrating away from Prisma to Drizzle which we just found out that Drizzle haselect with limit of 1Is there a cleaner way of selecting only one item with proper type safety than this? ```ts const usOption filter parametersHi! Love using drizzle so far! Had a quick question (not a bug): ``` const res = await ctx.databHow to delete with cascade?I'm using postgres with the following schema (reduced to the important parts): ``` export const worPSQL SQL query does not workUnable to achieve it with Drizzle helpers - I had to write my query in plain SQL. I'm trying to budrop tablesIs there a way to do a migration to drop tables? Other ORMs like Sequelize and Prisma have a conceptdb.query error with relationI have created a schema.ts, with two tables, with a one-to-one relationship. I have also create the How to transform to camelCase with json_agg()?As topic. I can't find any example on how to go about this. The closest I can find is https://orm.drHow to use select?I have a SQL query ``` SELECT row_to_json(departments.*) as department, row_to_jsonCount in relational queriesHow do I count in relational queries? For example if i'm querying db.query.posts.findMany() and wantCreate a type of VARCHAR[]I want to create a type for my column of `VARCHAR[]` but when I used Drizzle-Kit to generate it, I gSelect with relationIs there a way to get relations when using `db.select().from(table)`I can't get the `db.query.table.Any plan to support ClickHouse db?Any plan to support ClickHouse db? https://clickhouse.com/`where` inside relational queries `with` does not workHi there! I'm new to drizzle and tried out the relational queries. I want to do a nested `where` rIssue with 'insert on conflict do update where'I am using db.insert(lastHeaterMetricsTable) .values(heaterEntityWithMaxTime)