Drizzle Team•2y ago

`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],
    }),
}))

4 Replies

Dan•2y ago

how can you apply a where to a one relation? it always returns a single row

Fabian B.OP•2y 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};
`),

Dan•2y 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

Fabian B.OP•2y ago

@bloberenober I see, alright. Yeah will query it in reverse then. Thanks!

Gaming

Programming

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