DT
Drizzle Team•10mo ago
jakeleventhal

Best way to get count in extras

await db.query.productSets
  .findMany({
    extras: {
      productsCount:
        sql`(SELECT count(*) from ${products} WHERE "Product"."productSetId" = ${productSets.id})`.as(
          'productsCount'
        )
    },
    with: { products: true }
  })
await db.query.productSets
  .findMany({
    extras: {
      productsCount:
        sql`(SELECT count(*) from ${products} WHERE "Product"."productSetId" = ${productSets.id})`.as(
          'productsCount'
        )
    },
    with: { products: true }
  })
is there a better way to do this without the sql operator? I want to just use drizzle code (to make more complex queries). if i try to, i get weird issues. similarly, if i replace "Product"."productSetId" with ${products.productSetId}, it tells me productSetId does not exist on the ProductSet table (it uses the wrong table)
4 Replies
jakeleventhal
jakeleventhalOP•10mo ago
what i want is to be able to do something like: 
extras: {
  productsCount: db.select({value: count()}).from(products).where(...)
extras: {
  productsCount: db.select({value: count()}).from(products).where(...)
outranker
outranker•2mo ago
did you figure out how to do it?
TOSL
TOSL•2mo ago
extras: {
productCount: db.$count(products, eq(products.id, ....)}
}
extras: {
productCount: db.$count(products, eq(products.id, ....)}
}
TOSL
TOSL•2mo ago
https://orm.drizzle.team/docs/query-utils#count
Drizzle ORM - Utils
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Did you find this page helpful?