help

Relations, three level nested where?

Ttacomanator

5/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....

Bbloberenober

6/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

Ppanchocorderos

8/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

MMendy

8/10/2023

Yes, AFAIK this is deprecated since last version.

Bbloberenober

8/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

MMAST

8/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!

help

help

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 `findMa Simulate 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 ha select with limit of 1Is there a cleaner way of selecting only one item with proper type safety than this? ```ts const us Option filter parametersHi! Love using drizzle so far! Had a quick question (not a bug): ``` const res = await ctx.datab How to delete with cascade?I'm using postgres with the following schema (reduced to the important parts): ``` export const wor PSQL SQL query does not workUnable to achieve it with Drizzle helpers - I had to write my query in plain SQL. I'm trying to bu drop tablesIs there a way to do a migration to drop tables? Other ORMs like Sequelize and Prisma have a concept db.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.dr How to use select?I have a SQL query ``` SELECT row_to_json(departments.*) as department, row_to_json Count in relational queriesHow do I count in relational queries? For example if i'm querying db.query.posts.findMany() and want Create 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 g Select 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` r Issue with 'insert on conflict do update where'I am using db.insert(lastHeaterMetricsTable) .values(heaterEntityWithMaxTime)