DT
Drizzle TeamFabian B.

`where` inside relational queries `with` does not work

Hi there! I'm new to drizzle and tried out the relational queries. I want to do a nested where rule inside a with relation. But doing it is giving me type errors (Object literal may only specify known properties, and where does not exist in type) and it's just ignored by the orm. Maybe someone knows what I am doing wrong and why I don't get the option to do a where. Thanks in advance! My query: 
const spaceId = '...'

const found = await db.query.projects.findMany({
  with: {
    customer: {
      columns: {
        spaceId: true,
      },
      where: (customers, { eq }) => eq(customers.spaceId, spaceId),
    },
  },
}),
const spaceId = '...'

const found = await db.query.projects.findMany({
  with: {
    customer: {
      columns: {
        spaceId: true,
      },
      where: (customers, { eq }) => eq(customers.spaceId, spaceId),
    },
  },
}),
My schema (simplified): 
export const spaces = pgTable('spaces', {
    id: uuid('id').primaryKey().defaultRandom(),
    companyName: text('company_name').notNull(),
    companyTaxNumber: text('company_tax_number').notNull(),
    companyTaxIdNumber: text('company_tax_id_number'),
    companyTaxUstNumber: text('company_tax_ust_number'),
})

export const spacesRelations = relations(spaces, ({ one, many }) => ({
    customers: many(customers),
}))

export const projects = pgTable('projects', {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull(),
    status: projectsStatusEnum('status').notNull(), 
    customerId: uuid('customer_id').references(() => customers.id, {
        onDelete: 'set null',
    }),
})

export const projectsRelations = relations(projects, ({ one }) => ({
    customer: one(customers, {
        fields: [projects.customerId],
        references: [customers.id],
    }),
}))
export const spaces = pgTable('spaces', {
    id: uuid('id').primaryKey().defaultRandom(),
    companyName: text('company_name').notNull(),
    companyTaxNumber: text('company_tax_number').notNull(),
    companyTaxIdNumber: text('company_tax_id_number'),
    companyTaxUstNumber: text('company_tax_ust_number'),
})

export const spacesRelations = relations(spaces, ({ one, many }) => ({
    customers: many(customers),
}))

export const projects = pgTable('projects', {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull(),
    status: projectsStatusEnum('status').notNull(), 
    customerId: uuid('customer_id').references(() => customers.id, {
        onDelete: 'set null',
    }),
})

export const projectsRelations = relations(projects, ({ one }) => ({
    customer: one(customers, {
        fields: [projects.customerId],
        references: [customers.id],
    }),
}))
B
bloberenober•359d ago
how can you apply a where to a one relation? it always returns a single row
FB
Fabian B.•359d ago
@bloberenober Thanks for your quick answer! So my goal is "select all projects where projects.customer.space.id is equal to..." So logially the where must apply to the projects, so ideally like this (invalid syntax): 
db.query.projects.findMany({
  with: {
    customer: {
      columns: {
        spaceId: true,
      }
    }
  },
  where: (projects, { eq }) => eq(projects.customer.spaceId, spaceId),
}),
db.query.projects.findMany({
  with: {
    customer: {
      columns: {
        spaceId: true,
      }
    }
  },
  where: (projects, { eq }) => eq(projects.customer.spaceId, spaceId),
}),
hope this better explains what my goal is. Another way I guess would be to db.query.customers.findMany , apply the where spaceId to the root and then join into the projects. If I run the sql directly, it works fine. 
db.execute(sql`
  SELECT p.*
  FROM projects p
  JOIN customers c ON p.customer_id = c.id
  WHERE c.space_id = ${spaceId};
`),
db.execute(sql`
  SELECT p.*
  FROM projects p
  JOIN customers c ON p.customer_id = c.id
  WHERE c.space_id = ${spaceId};
`),
B
bloberenober•359d ago
You currently cannot filter by a nested relation field, unfortunately You can query in reverse, if it helps - select all customers where the space ID equals X and include projects in them
FB
Fabian B.•359d ago
@bloberenober I see, alright. Yeah will query it in reverse then. Thanks!
Want results from more Discord servers?
Add your server
More Posts
Issue with 'insert on conflict do update where'I am using db.insert(lastHeaterMetricsTable) .values(heaterEntityWithMaxTime) Duplicate relations when using `with`I'm running into a problem where some duplicate values are being returned through a relation with `wUsing BIN_TO_UUID / UUID_TO_BINI’m trying to understand the best way to use `BIN_TO_UUID` and `UUID_TO_BIN` (MySQL). The below is drizzle-kit drop config file does not existI have a `/foo/drizzle.config.ts` file as suggested in the docs (https://orm.drizzle.team/kit-docs/Related object is not typed correctlyI have the following schema ``` export const menus = pgTable('menus', { id: uuid('id').primaryKey(Custom vector type with pgvectorI'm trying to use the `pgvector` extension with a custom type but am running into an issue with the Missing 'with' clause additional operators (where, limit, offset, etc)Have been digging into the docs and the latest update. ❣️ In the docs (https://orm.drizzle.team/ddrizzle-zod type errors with latest versionsI updated all drizzle deps to latest and having type errors when using drizzle-zodI am confused on how the new relational queries worksIn the docs I see the following ``` import { pgTable, serial, text, integer, boolean } from 'drizzleis not assignable to type 'DrizzleD1Database'Hello This last release was amazing. Making joins simple was the missing piece for me. However, IDrizzle kit generate gives error after upgradeI have updated drizzle-orm to 0.26.0 and drizzle-kit to 018.0. I have defined relations according toIntrospection error with pgHello, I am trying to introspect my already created DB but when I run the cli command I get: ``` > dHow to declare PostgreSQL extensions/plugin?Reference: https://www.prisma.io/docs/concepts/components/prisma-schema/postgresql-extensions Is thHow to consume existing supabase migration?I have a supabase migrations and would like to reuse them as a starter for drizzle. When trying to pPrisma Studio style db explorerHey there! Anyone aware of a prisma studio style db-playground / db-admin-dashboard that works withIncorrect return type findFristHi! I just upgraded to `26.0` and I'm refactoring queries to `findFirst` where needed. But the retuRelations module - or conditionHi there! I have a teams table, an users table, and a team_members table that connects these two. AAre foreign key polyfills for PlanetScale supported by Drizzle?Hi Prisma supports this (although slow). See: https://www.prisma.io/docs/guides/database/planetscal