Select where one->many relation has at least one matching a condition

I'm having a hard time wrapping my head around Drizzle, coming from Prisma. I'm trying to do something like this:

const games = await prisma.db.game.findMany({
    where: {
      products: {
        some: {
          hidden: false,
        },
      },
    },
    select: {
      id: true,
      name: true,
    },
  });

const games = await prisma.db.game.findMany({
    where: {
      products: {
        some: {
          hidden: false,
        },
      },
    },
    select: {
      id: true,
      name: true,
    },
  });

I can't seem to figure out how to do this in Drizzle. Could anyone give me some directions on getting it done? Thanks

10 Replies

neoneyOP•12mo ago

const drizzleGames = await drizzle.query.games.findMany({
  where: (games, { inArray }) =>
    inArray(
      games.id,
      drizzle
        .select({ data: schema.products.id })
        .from(schema.products)
        .where(eq(schema.products.hidden, false))
    ),
});

const drizzleGames = await drizzle.query.games.findMany({
  where: (games, { inArray }) =>
    inArray(
      games.id,
      drizzle
        .select({ data: schema.products.id })
        .from(schema.products)
        .where(eq(schema.products.hidden, false))
    ),
});

something like this i'd guess? wait wrong data, supposed to be gameId seems to work

George•12mo ago

@neoney Declare relations and you can use drizzle like prisma https://orm.drizzle.team/docs/rqb#declaring-relations

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

neoneyOP•12mo ago

I did declare relations 🤔 There's no examples of queries using them there Or I can't find them so I'm confused

George•12mo ago

https://orm.drizzle.team/docs/rqb#querying

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

George•12mo ago

with comes from the defined relations

neoneyOP•12mo ago

Yea but I need a where on it I think with just adds to the returned data doesn't it?

neoneyOP•12mo ago

For example I'd need something like this, but to overall not include the posts if theres no comments before particular date

neoneyOP•12mo ago

The code I wrote seems to work but it also seems complicated Maybe i am misunderstanding... let me double check Nah I was right No clue if its possible without doing that way still Anyone has a clue?

francis•12mo ago

figure out how you would write it in sql first, then translate to drizzle how I would do it: - left join onto products where product id = game.id, AND product is not hidden - WHERE product.id is not null - group by game.id This will get you one result per game row that has a not-hidden product associated with it.

neoneyOP•12mo ago

yea i did that kinda its similar to a join

Gaming

Programming

Select where one->many relation has at least one matching a condition