Theo's Typesafe Cult•16mo ago

Pagination in Prisma query not working correctly near end of results

I'm using Prisma to query a Postgres database and implement pagination. However, I'm running into an issue where the pagination doesn't seem to be working correctly near the end of the results. Here's the query I'm using for reference:

get: protectedProcedure
    .input(
      z.object({
        pageSize: z.number().min(1).max(100),
        pageIndex: z.number().min(0),
      })
    )
    .query(async ({ input, ctx }) => {
      return await prisma.subscription.findMany({
        where: {
          clients: {
            user_email: ctx.session.user.email,
          },
        },
        include: {
          lines: {
            select: {
              phone_number: true,
              sim_number: true,
              sim_status: true,
            },
          },
        },
        take: input.pageSize,
        skip: input.pageIndex,
      });
    }),

get: protectedProcedure
    .input(
      z.object({
        pageSize: z.number().min(1).max(100),
        pageIndex: z.number().min(0),
      })
    )
    .query(async ({ input, ctx }) => {
      return await prisma.subscription.findMany({
        where: {
          clients: {
            user_email: ctx.session.user.email,
          },
        },
        include: {
          lines: {
            select: {
              phone_number: true,
              sim_number: true,
              sim_status: true,
            },
          },
        },
        take: input.pageSize,
        skip: input.pageIndex,
      });
    }),

The issue I'm experiencing is that as I get closer to the end of the dataset, the page size starts to decrease by 1 for each page, until the last page only shows 1 record. I've double-checked that the values being passed to the query are correct, so I'm not sure what's causing this issue. I've read that using skip to implement pagination can lead to performance issues, but I don't understand why it would cause this particular issue. Can someone explain what might be causing this issue and how I can fix it? Thanks in advance for any help!

2 Replies

mattddean•16mo ago

Maybe do cursor pagination with https://trpc.io/docs/useInfiniteQuery

useInfiniteQuery | tRPC

- Your procedure needs to accept a cursor input of any type (string, number, etc)

Mendy•16mo ago

Yeah, If I can't figure it out soon I'll make the switch

Gaming

Programming

Pagination in Prisma query not working correctly near end of results