Drizzle Team•14mo ago

How to filter by a column in a related table?

Example: list all recipes with a specific ingredient Also, I wish to include all ingredients of each matched recipe in the response

4 Replies

Angelelz•14mo ago

An inner join?

Angelelz•14mo ago

https://orm.drizzle.team/docs/joins#inner-join

Joins [SQL] - DrizzleORM

Drizzle ORM | %s

ThomasOP•14mo ago

Thanks, but inner join doesn't seem to support including the ingredients in the response. Got it working like this:

const all = await db.query.recipes.findMany({
    where: like(products.display, '%cheese%'),
    with: {
      ingredients: {
        with: {
          product: true,
        },
      },
    },
  })

const all = await db.query.recipes.findMany({
    where: like(products.display, '%cheese%'),
    with: {
      ingredients: {
        with: {
          product: true,
        },
      },
    },
  })

Michael Dem•14mo ago

I use a raw sql query when filtering by a related table. Something like "where recipes.id in (select recepeId form ingredients where ingredients.name like %cheese%)"

Gaming

Programming

How to filter by a column in a related table?