I need to implement cursor based pagination on an endpoint which accepts a search query. The problem is that since rows will be ranked based on a relevance score derived from a full-text search in natural language, IDs won't be sequential. Does anyone know how I can achieve this in EF Core with Pomelo.EntityFrameworkCore.MySql?
-
See if Shorpy could help.Chin. Udara– Chin. Udara2025-03-19 15:53:48 +00:00Commented Mar 19 at 15:53
Add a comment
|
1 Answer
You have the "relevance score", correct? And a sequential id?
"Remember where you left off" and use the pair (score, id) to keep track of the last item on one page (or equivalently, the first item on the next page).
More discussion: https://mysql.rjweb.org/doc.php/pagination
5 Comments
iKingNinja
Sorry for the late reply. I do have sequential IDs, but the problem is they won't be sequential since the relevance score is more important. Consider the case where each page contains 2 rows and that there are 4 rows: ID = 1, Score = 2 ID = 2, Score = 3 ID = 3, Score = 4 ID = 4, Score = 3 Given I want to return from most to least relevant, the first request will yield [(ID = 3, Score = 4), (4, 3)], the second request will look for rows with
ID > 4 (last) AND Score <= 3; with this query rows 1 and 2 won't be returned because their ID is lesser than 4.
Rick James
@iKingNinja - For effectively ORDER BY score DESC, id ASC: [4,3], [3,2], [3,4], [2,1] or for ORDER BY score DESC, id DESC: [4,3], [3,4], [3,2], [2,1] (Where the pairs are [score,id]
iKingNinja
I don't get what you're saying. My question is how will I handle pages where the first item has the same relevance score as the last item in the previous one since score based sorting makes IDs non-sequential?
Rick James
@iKingNinja - The combination of score and id is unique for each page and provides a predictable ordering to the pages.
iKingNinja
So is there a one-fits-all query? Could you make an example with some dummy data where ordering by ID won't exclude any row with the relevant SQL query? Thanks.