Question 89517 in PostgreSQL Question posted in
The official documentation can be found here.

Pagination in Prisma query not working correctly near end of results – Postgresql

I’m using Prisma to query a PostgreSQL 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,
      });
    }),

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.

Example

For a dataset of 15 results, I set pageSize to 10 and used the following offset:

  • When pageIndex is set to 0, I expected to see the first 10 results.

  • When pageIndex is set to 1, I expected to see the last 5 results.

However, this is what I actually got:

  • When pageIndex is set to 0, I got the first 5 results.

  • When pageIndex is set to 1, I got the next 4 results.

  • When pageIndex is set to 2, I got the next 3 results.

  • When pageIndex is set to 3, I got the next 2 results.

  • When pageIndex is set to 4, I got the next 1 result.

The pagination seems to be decreasing the number of results returned for each page as I get closer to the end of the dataset.

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?

Any help or guidance you can provide would be greatly appreciated. Thank you for your time and expertise!

Tags:

2

Answers


  1. 0

    it is all make sense, in your table there are 5 rows. for the first time, prisma will skip 0 and take 10 at least so, the answer is 5 rows. next round prisma will skip 1 then the answer will be 4 rows.
    your prima skip value should be:

    skip: input.pageIndex * input.paseSize
    Login or Signup to reply.
  2. 0

    The solution was

    skip: input.pageIndex * input.pageSize

    Since skip earlier was input.pageIndex, basically on each page there were more items to skip. This was the idea behind my comment to the question and since it turned out to be correct, I converted it into an answer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search