Nested where filter, how to not include empty

The following almost works

const query = db.query.productionItem.findMany({
  with: {
    itemStages: {
      where: eq(itemStage.crew_id, input.crew_id),
    },
  },
});

const query = db.query.productionItem.findMany({
  with: {
    itemStages: {
      where: eq(itemStage.crew_id, input.crew_id),
    },
  },
});

I get only those productionItems with itemStage of particular crew_id, but I also get those with no itemStage's so query result has itemStages: []. How do I only include if crew_id equal and productionItem has itemStage

4 Replies

Angelelz•13mo ago

The where that you have only filters the itemStages You need a where for the productionItems For that to work, you either use a subquery in the where or you have to turn to the regular crud API: db.select

Nxia618OP•13mo ago

Not following what " use a subquery in the where " means?

Angelelz•13mo ago

Take a look at this thread: https://discord.com/channels/1043890932593987624/1166190702984708126 There are several examples in there for a very similar query In this message is the subquery I was talking about: https://discord.com/channels/1043890932593987624/1166190702984708126/1166201121107345478

Nxia618OP•13mo ago

thanks, I'll take a look

Gaming

Programming

Nested where filter, how to not include empty