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

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

how can you apply a where to a one relation?

it always returns a single row

@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),
}),

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

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

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