The data looks as follows:

13 users with id's 2-13. User 2 got 2 likes, user 10 got 2 likes, user 3 got 1 like. The rest didn't get any likes.

Prisma query looks like this:

return this.prisma.user.findMany({
  skip: Number(page) * Number(size),
  take: Number(size),
  orderBy: { likesReceived: { _count: "desc" } 
});

When I send a query to the database, ordering by likesReceived I get these responses:

User 14 appears twice, and user 5 is missing. Why?

Additional sorting by id fixes the problem:

return this.prisma.user.findMany({
  skip: Number(page) * Number(size),
  take: Number(size),
  orderBy: [{ likesReceived: { _count: "desc" } }, { id: "asc" }],
});

Results:

When is specifying a second parameter in orderBy with pagination necessary for it to work well?

CodePudding user response：

I had a similar issue with Laravel using sqlserver.

Laravel was doing a different query for the first page than the subsequent pages. For page 1 they used...

SELECT TOP 100 * FROM users

while for subsequent pages they used row_number(), something like...

SELECT * FROM (
    SELECT 
        ROW_NUMBER() row_num, 
        *
    FROM
        users
    ) u    
WHERE
    row_num > 100 AND row_num <=201;

Sqlserver doesn't do a default order by Default row order in SELECT query - SQL Server 2008 vs SQL 2012, rather each time it will choose the most optimized way. Therefore on the page 1 query using TOP it chose one way to order and on page 2 with row_number() it chose a different way to order. Thereby returning duplicate results in page 2 that were already in page 1. This was true even though I had many other order bys.

Mysql also seems not to have a default order by SQL: What is the default Order By of queries?.

I don't know if Prisma does the same thing with mysql. Printing out the queries may shed light on if different queries are used for different pages.

Either way if you're using pagination it may make sense, to do as you mentioned and to always use id as a final order by. Like this even if your other intended order bys allow the same record to be on multiple pages the final order by id will ensure that doesn't occur, since now you're forcing it to order by ids instead of choosing a more optimal approach that doesn't order by ids.

In your case since user 14 has 0 likes it can be on any page after 2, 10 and 3 and still satisfy your likesReceived orderBy. But with the id order by then it'll always be on page 2, since page 1 will now have 4 and 6 as the last records, instead of 14, due to the 2nd orderBy of id.