Get total row count in select

Im trying to get the total row count of a joined table.

5 Replies

If it's an aggregation like count(*) you can't use it in relational queries You have to use the regular CRUD API

maybe with 'extra':

extras: {
      count: sql`(SELECT count(*) from ${your_table})`.as('count') // or any sub query. Keep the sub query between parenthesis
   }

extras: {
      count: sql`(SELECT count(*) from ${your_table})`.as('count') // or any sub query. Keep the sub query between parenthesis
   }

https://orm.drizzle.team/docs/rqb#include-custom-fields

nqhtrung•17mo ago

I ran into same issue and using extras doesn’t work, I have to use the regular CRUD api

rogueturnip•17mo ago

Count seems like a very common thing that people want. in the findMany query why not have a "with" option for relations? like

with: {
  count:true
  comments: {
    count: true
  }
}

This would then return an object instead of array 
{
  count: 23
  posts: [
    {
      id: ...
      ...
      comments: {
        count: 1000
        comments: [...]
      }
    }
  ]
}

with: {
  count:true
  comments: {
    count: true
  }
}

This would then return an object instead of array 
{
  count: 23
  posts: [
    {
      id: ...
      ...
      comments: {
        count: 1000
        comments: [...]
      }
    }
  ]
}

Just a thought on a future feature that could help out with the many "count" questions I'm seeing (since I'm looking for a nice way too)

nqhtrung•17mo ago

here is how I get total count of the records returned. I also did some pagination here.

const condition = and(
      input?.category ? ilike(products.category, input.category) : undefined,
      input?.name ? ilike(products.name, `%${input.name}%`) : undefined
    );

const [countResult] = await db
  .select({
    count: sql`count(*)`.mapWith(Number).as("count"),
  })
  .from(products)
  .where(condition);

const productList = await db.query.products.findMany({
  limit: input.perPage,
  offset: input.page * input.perPage,
  where: condition,
  orderBy: products.name,
});

const totalPage = Math.ceil((countResult?.count ?? 0) / input.perPage);

return {
  totalPage,
  perPage: input.perPage,
  data: productList,
};

const condition = and(
      input?.category ? ilike(products.category, input.category) : undefined,
      input?.name ? ilike(products.name, `%${input.name}%`) : undefined
    );

const [countResult] = await db
  .select({
    count: sql`count(*)`.mapWith(Number).as("count"),
  })
  .from(products)
  .where(condition);

const productList = await db.query.products.findMany({
  limit: input.perPage,
  offset: input.page * input.perPage,
  where: condition,
  orderBy: products.name,
});

const totalPage = Math.ceil((countResult?.count ?? 0) / input.perPage);

return {
  totalPage,
  perPage: input.perPage,
  data: productList,
};

Gaming

Programming

Get total row count in select