Drizzle Team•9mo ago

How to get where relation has no rows

const products = await db.query.masterProducts.findMany({
  limit: pageSize,
  offset: page * pageSize,
  where: whereFilter
});

const products = await db.query.masterProducts.findMany({
  limit: pageSize,
  offset: page * pageSize,
  where: whereFilter
});

The masterProducts table has a one to many relationship with unitCosts (one master product has many unit costs). How do I modify whereFilter if I want to only fetch masterProducts rows that have no associated unitCosts?

4 Replies

jakeleventhal•9mo ago

bump

Angelelz•9mo ago

You'd need a subquery that returns a list of masterProducts.id from the unitCosts filter And the where would be any masterProducts.id not in that "array"

jakeleventhal•9mo ago

that's what i figured but isn't that terribly inefficient to fetch every single row for the sub query? does this mean a schema update is probably warranted

Angelelz•9mo ago

It is my understanding that in later versions, pg query planner is smart enough to detect subqueries that are not correlated and run them once. If you weren't using RQB, you could use CTEs to make sure it is actually running once In any case, nothing that some benchmarks or performace tests wouldn't give you an answer Or even wherever PG's explain analyze equivalent is from mysql

Gaming

Programming

How to get where relation has no rows