Drizzle Team•5mo ago

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)

1 Reply

jakeleventhal•5mo 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(...)

Gaming

Programming

Best way to get count in extras