Drizzle Team•11mo ago

How to get the count of relations?

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

I have another table inventoryItems and there is a one-to-many relationship where one masterProduct can have many inventoryItems and one inventoryItem is linked to one and only one masterProduct on the masterProductId field. How can I include a count of inventoryItems in this query? I just want a number of how many inventoryItems have the masterProductId field matching the id field for each row in the masterProducts query

17 Replies

jakeleventhalOP•11mo ago

bump

Angelelz•11mo ago

You can write a subquery that counts everything with the same where but no limit or offset. You can probably put that in the extras

jakeleventhalOP•11mo ago

As of now aggregations are not supported in extras, please use core queries for that.

@Angelelz given that, is there a better way to do this query than what i currently am doing?

await db
  .select({
    inventoryItemCount: count(inventoryItems.id),
    masterProduct: masterProducts
  })
  .from(masterProducts)
  .leftJoin(inventoryItems, eq(masterProducts.id, inventoryItems.masterProductId))
  .groupBy(sql`2`)

await db
  .select({
    inventoryItemCount: count(inventoryItems.id),
    masterProduct: masterProducts
  })
  .from(masterProducts)
  .leftJoin(inventoryItems, eq(masterProducts.id, inventoryItems.masterProductId))
  .groupBy(sql`2`)

or a way to flatten out the masterProduct and just add a separate field rather than having to have a nested masterProduct field

Angelelz•11mo ago

Yeah, they say that, but you can put a subquery in there, the subquery can do whatever you want I think So, what you want is the masterProduct, with a count of how many inventoryItems it has?

jakeleventhalOP•11mo ago

yes having trouble piecing it together in an efficient way

Angelelz•11mo ago

You got it, you're doing it right If you want it flatter you could do this:

await db
  .select({
    ...getTableColums(masterProducts),
    count: count(inventoryItems.id)
  })
  .from(masterProducts)
  .leftJoin(inventoryItems, eq(masterProducts.id, inventoryItems.masterProductId))
  .groupBy(masterProducts.id)

await db
  .select({
    ...getTableColums(masterProducts),
    count: count(inventoryItems.id)
  })
  .from(masterProducts)
  .leftJoin(inventoryItems, eq(masterProducts.id, inventoryItems.masterProductId))
  .groupBy(masterProducts.id)

I think that should be type safe Working from memory here

jakeleventhalOP•11mo ago

and what if i want to apply a filter to the inventory items? can i do that within the leftjoin

  .leftJoin(inventoryItems, and(
    eq(masterProducts.id, inventoryItems.masterProductId),
    eq(inventoryItems.someField, 'test'),
  ))

  .leftJoin(inventoryItems, and(
    eq(masterProducts.id, inventoryItems.masterProductId),
    eq(inventoryItems.someField, 'test'),
  ))

Angelelz•11mo ago

I believe, the on clause accepts the same type of stuff that the where accepts, so yes Give it a try and let me know

jakeleventhalOP•11mo ago

and what if i wanted to add a new column: inventoryItems that would be a list of all the inventory items as well? the returned type is just a single item, not an array

.select({
  ...getTableColumns(masterProducts),
  inventoryItems: getTableColumns(inventoryItems) -> NOT returned as an array
})

.select({
  ...getTableColumns(masterProducts),
  inventoryItems: getTableColumns(inventoryItems) -> NOT returned as an array
})

Angelelz•11mo ago

You'll hit a road block in that approach remember that for the count to work properly, you need to group by For what you want, you'll probably need to run the count as a window function Oh wait, you want the inventory items as an array?

jakeleventhalOP•11mo ago

yes as a separate point from count

Angelelz•11mo ago

You might be able to use a json aggregate function, the same as count With the same group by, I guess that should work

jakeleventhalOP•11mo ago

yeah, sqlJSON_AGG(DISTINCT ${inventoryItems}) works

Angelelz•11mo ago

Nice, I would only say that distinct is usually a performance killer, use it only if you really need it

jakeleventhalOP•11mo ago

without distinct its returning more rows than necessary same with the count query

Gary, el Pingüino Artefacto•11mo ago

are there any plans for managing this with relational queries? if it is possible of course

Angelelz•11mo ago

There are 2 issues tracking this feature: https://github.com/drizzle-team/drizzle-orm/issues/1098 and https://github.com/drizzle-team/drizzle-orm/issues/961

GitHub

[FEATURE]: Enhance with keyword to support fetching relation coun...

Describe what you want Title: Enhance with keyword to support fetching relation counts Body: Feature Description To streamline the querying process and reduce the need for custom SQL for common sce...

GitHub

[FEATURE]: withCount · Issue #961 · drizzle-team/drizzle-orm

Describe what you want It would be nice to have a quick way to get the count of a relationship like Eloquent or Prisma const res = await db.query.users.findMany({ columns: {}, withCount: { posts: t...

Gaming

Programming

How to get the count of relations?