Drizzle Team•15mo 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

bloberenober•15mo ago

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

Fabian B.•15mo 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};
`),

bloberenober•15mo 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.•15mo ago

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

Gaming

Programming

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