How to know total number of the filtered items when the indexed pagination is demanded?

[TokugawaTakeshi]

Question

Assume that besides the pagination, there is the filtering (where: {}). Here we need to distinct:

1. Items number per pagination page (usually known from the request data)
2. Total items in selection (filtered items number, by other words, total items count in all pagination pages)
3. Total rows count in database table. In most cases, this number is not even with the second one when the filtering is applied.

Now, how to get the second number with the best performance?
If possible, want to avoid the double request.

[kuishou68]

The standard approach is to use $transaction to run both queries in parallel — one for the paginated items, one for the filtered total count — in a single round-trip:

const where = { /* your filter conditions */ };
const page = 1;
const pageSize = 20;

const [items, total] = await prisma.$transaction([
  prisma.post.findMany({
    where,
    skip: (page - 1) * pageSize,
    take: pageSize,
    orderBy: { createdAt: 'desc' },
  }),
  prisma.post.count({ where }),
]);

return {
  items,
  total,          // filtered total (your "second number")
  totalPages: Math.ceil(total / pageSize),
};

prisma.post.count({ where }) gives you exactly the filtered item count across all pages — not the total rows in the table. Both queries share the same where filter, so they stay in sync.

This avoids a double round-trip because $transaction([...]) sends both queries together.

[TokugawaTakeshi]

I suppose it is the best solution which possible with current Prisma. Thank you for the answer!