How to get total documents count along with pagination

[aakashkag]

Hi,
Lets say we have user table.
Want to apply pagination on user table.

query{
  users(
    where:{
    Assigned_Role:{some:{user_role:{equals:"Student"}}}
    },
    skip:5
    take:5
    cursor:{user_id:5}
  ){
    user_id
    user_name
}
} 

Now I want to get total documents count along with pagination result. is it possible?

[ryands17]

Hey @aakashkag 👋
You could perform this with a transaction here and return the result as per your schema:

await prisma.transaction([
    prisma.user.count(),
    prisma.user.findMany({
      take: 5,
      cursor: {
        id: 5,
      },
    }),
  ])

[ChristianRich]

Can we kindly request the authors of Prisma to address this? E.g include a _count param when doing paginated queries using skip and take params?

[zaverden]

issue exists: #7550

[transitive-bullshit]

I agree it's pretty annoying that this extremely common pattern requires two queries.

[motdde]

Thank you for sharing this. However, this method may not work when there are conditions involved. In such cases, you must ensure that the same condition is passed for the count to be accurate.

Edit: I am here wondering why we have take and skip options in count that only return a number. What will be the use case?

[benbatuu]

if you want to just a number of the data, you can just use : prisma.schemaName.count();

[ChristianRich]

Is there a solution to this problem that doesn't involve making two queries?

I'm aware that you can go prisma.myModel.count() but that's still an additional query and not efficient.
https://lifesaver.codes/answer/parameterized-count-queries-252

The only other solution I can think of is executing raw SQL but that kind of defy the purpose of using an ORM in the first place.

Pagination and total document count is a trivial DB use case and should be supported by the otherwise excellent Prisma project.

[cesarvspr]

please ping me if any update comes up

[andreasvh-conceto]

any ticket created for this issue? This would be awesome :)

[ymsaadi]

We need a better way to get count with pagination than the accepted answer.

[byoungd]

Any update for this?
I think include count is so important.

Hope team give us a better answer.

[agustints]

Any solution?

[omidh28]

Shouldn't this topic be moved to an issue?

[bsplosion]

Already was one, currently open: #7550

[laurent512]

up, requested feature for me as well :)

[vsinghipcium]

must have

[maple5233]

Is using count the only way currently?

[eavonius]

This is really frustrating! Something like sequelize's findAndCountAll would be perfect here.

[eavonius]

I may need to do an executeRaw for now. Which is kind of a bummer as it defeats the purpose of an ORM.

If we had an override for findMany called findAndCountMany that returned your result set, plus added a column to the query “COUNT(*) AS PrismaResultCount” that was then returned as a count value in an associative array along with the full results as a rows value, that would seem like it may work.

I haven’t cracked open the code for how prisma generates queries yet so I’m guessing that’s an oversimplification.

[bsplosion]

@eavonius in case you're using that in production, note that you'll want to use a windowed a count(*) over () instead of count(*) - the latter will not return the total unpaginated count, just the count within the page (assuming you had aggregates). The window function can be okay, though it have some significant performance implications for big datasets and can also have problems if the offset and/or limit are not defined to fit the data.

[edarcode]

yo emocionado con prisma y salen con esta bobada, no entiendo que les costaba contar la bendita consulta xD

[SC0d3r]

Would love to see this feature, my suggestion is:

const products = await prisma.product.findMany({
  skip: 15,
  take: 3,
  select: { /*...someSelections*/ }
  _count:{
    $total: true,
  }
})

const totalProducts = products._count.$total // this should give the total results

[Toshiro00]

or for each column. I don't know if there is already have feature like that.

_count: {
        $each: true
    }

[jmakinin]

this question is definitely not answered,
the very answer marked as correct defeats the purpose of an orm making queries much more simplified and efficient. reason being that raw queries solve this issue rather better than prisma.
it should definitely be moved to an issue

[zaverden]

this is an issue #7550

[Zoot01]

Here is my suggested workaround as of now. Let me know if anyone has any better suggestions.

const getPaginatedResults = async (
  sortType: string | undefined | null,
  column: string,
  searchQuery: string,
  skip: number,
  take: number,
) => {
  const submission = await prisma.$transaction([
    prisma.submissions.count({
      where: {
        [column]: {
          mode: 'insensitive',
          contains: searchQuery,
        },
      },
    }),
    prisma.submissions.findMany({
      skip: skip,
      take: take,
      where: {
        [column]: {
          mode: 'insensitive',
          contains: searchQuery,
        },
      },
      orderBy: {
        [column]: sortType ?? 'asc',
      },
    }),
  ]);

  return {
    total: submission[0] ?? 0,
    data: submission[1],
  };
};

[mdinjamul]

This worked for me

import { Prisma } from '@prisma/client';
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

const findAll = async (userId: String) => {
  const query: Prisma.categoriesFindManyArgs = {
    where: {
      user_id: userId,
    }
  };
  const [categories, count] = await prisma.$transaction([
    prisma.categories.findMany(query),
    prisma.categories.count({ where: query.where })
  ]);

  return {
    pagination: {
      total: count
    },
    data: categories
  };
};

[kyle-turn2]

@injamulhaque535 how did you get this to work? I get a Property 'UserFindManyArgs' does not exist on type 'typeof Prisma'. I've tried importing import { Prisma } from "@prisma/client"; and import prisma from "@/lib/prisma";. What does your tsconfig.json look like?

[kyle-turn2]

Ok, it does work, it's just that Typescript is not happy.

[maykon-oliveira]

I don't know if someone is still looking for a better solution for this, but I found a library in the Prisma documentation that gets rid of all this stuff.

https://github.com/sandrewTx08/prisma-paginate

[anhnch]

It calls 2 queries under the hood

[ndom91]

Based on this comment in a similar issue in this repo, I wanted to share my Prisma client extension workaround for this issue, and types are inferred correcltly as well 🤞

// /src/lib/db.ts

import { Prisma, PrismaClient } from '@prisma/client'

const prisma = new PrismaClient().$extends({
  name: 'findManyAndCount',
  model: {
    $allModels: {
      findManyAndCount<Model, Args>(
        this: Model,
        args: Prisma.Exact<Args, Prisma.Args<Model, 'findMany'>>
      ): Promise<[Prisma.Result<Model, Args, 'findMany'>, number]> {
        return prisma.$transaction([
          (this as any).findMany(args),
          (this as any).count({ where: (args as any).where })
        ]) as any;
      }
    }
  }
});

export default prisma

Usage:

// /src/components/bookmarks.tsx

const [data, count] = await prisma.bookmark.findManyAndCount({
  take: parseInt(limit) + parseInt(skip),
  skip: parseInt(skip),
  where: { userId: session?.user?.userId },
  include: {
    category: true,
    tags: { include: { tag: true } },
  },
  orderBy: { createdAt: "desc" },
})

[zwhitchcox]

@ndom91 The only problem with this is that it doesn't preserve the types for the regular prisma models. Solution:

import { Prisma, PrismaClient } from '../client/index.js';

const findManyAndCount = {
  name: 'findManyAndCount',
  model: {
    $allModels: {
      findManyAndCount<Model, Args>(
        this: Model,
        args: Prisma.Exact<Args, Prisma.Args<Model, 'findMany'>>
      ): Promise<
        [
          Prisma.Result<Model, Args, 'findMany'>,
          number,
          Args extends { take: number } ? number : undefined
        ]
      > {
        return prisma.$transaction([
          (this as any).findMany(args),
          (this as any).count({ where: (args as any).where }),
        ]) as any;
      },
    },
  },
};

function createExtendedClient() {
  return new PrismaClient(options)
    .$extends(findManyAndCount);
}

type ExtendedPrismaClient = ReturnType<typeof createExtendedClient>;
type PC = Omit<ExtendedPrismaClient, keyof PrismaClient> & PrismaClient

export const prisma =  createExtendedClient()  as unknown as PC;

The Omit is to prevent recursive max depth overflow (not sure why this happens though)

[ndom91]

Still works, nice!

[ndom91]

Did find a type error though now, it now claims my schema's don't have findManyAndCount on them :/

[zwhitchcox]

Ok (panting) in this code

• findManyAndCount and findMany types both work
• you can go to the definition of each type
• does not cause maximum depth exceeded errors when type checking

which I think fulfills all the requirements, at least the ones I have.

type FindManyAndCountType = typeof findManyAndCount.model.$allModels.findManyAndCount;

// Apply the inferred types to the extended models
type ModelsWithCustomMethods = {
  [Model in keyof PrismaClient]: PrismaClient[Model] extends { findMany: (...args: any[]) => Promise<any> }
    ? {
        findManyAndCount: FindManyAndCountType;
      } & PrismaClient[Model]
    : PrismaClient[Model];
};

type ExtendedPrismaClient = PrismaClient & ModelsWithCustomMethods;

function createExtendedClient(): ExtendedPrismaClient {
  const prisma = new PrismaClient(options) as unknown as ExtendedPrismaClient;
  prisma.$extends(findManyAndCount);
  return prisma;
}

const prisma: ExtendedPrismaClient = createExtendedClient();

to test the types:

// Testing the types
prisma.appointment.findManyAndCount({ where: { id: 1 } }); // Should work
prisma.appointment.findMany({ where: { id: 1 } }); // Should work
prisma.appointment.findManyAndCount({ whe: { id: 1 } }); // Should error
prisma.appointment.findMany({ we: { id: 1 } }); // Should error

@ndom91

[ndom91]

Ok (exhales) purrrfect 👌 😂

[MkDierz]

hey im trying to use this with edge and prisma accelerate but dosent work
im using nextjs 14 with postgresql

import { PrismaClient } from "@prisma/client/edge";
import { withAccelerate } from "@prisma/extension-accelerate";
import { Prisma } from "@prisma/client";

const findManyAndCount = {
  name: "findManyAndCount",
  model: {
    $allModels: {
      findManyAndCount<Model, Args>(
        this: Model,
        args: Prisma.Exact<Args, Prisma.Args<Model, "findMany">>
      ): Promise<
        [
          Prisma.Result<Model, Args, "findMany">,
          number,
          Args extends { take: number } ? number : undefined
        ]
      > {
        return prisma.$transaction([
          (this as any).findMany(args),
          (this as any).count({ where: (args as any).where }),
        ]) as any;
      },
    },
  },
};

type FindManyAndCountType =
  typeof findManyAndCount.model.$allModels.findManyAndCount;

type ModelsWithCustomMethods = {
  [Model in keyof PrismaClient]: PrismaClient[Model] extends {
    findMany: (...args: any[]) => Promise<any>;
  }
    ? {
        findManyAndCount: FindManyAndCountType;
      } & PrismaClient[Model]
    : PrismaClient[Model];
};

type ExtendedPrismaClient = PrismaClient & ModelsWithCustomMethods;

const prismaClientSingleton = () => {
  const prisma = new PrismaClient() as unknown as ExtendedPrismaClient;
  prisma.$extends(withAccelerate());
  prisma.$extends(findManyAndCount);
  return prisma;
};

declare global {
  var prismaGlobal: undefined | ReturnType<typeof prismaClientSingleton>;
}

const prisma = globalThis.prismaGlobal ?? prismaClientSingleton();

export default prisma;

if (process.env.NODE_ENV !== "production") globalThis.prismaGlobal = prisma;

 ⨯ src/lib/query.ts (5:35) @ findManyAndCount
 ⨯ TypeError: _db__WEBPACK_IMPORTED_MODULE_0__.default.wallet.findManyAndCount is not a function
    at readWallets (./src/lib/query.ts:9:75)
    at Page (wallet/page.tsx:15:79)
    at stringify (<anonymous>)
    at AsyncLocalStorage.run (node:async_hooks:346:14)
  3 |
  4 | export const readWallets = async (skip: number = 0, limit: number = 10) => {
> 5 |   const data = await prisma.wallet.findManyAndCount({
    |                                   ^
  6 |     take: limit + skip,
  7 |     skip: skip,
  8 |   });