通过API对未分类和过滤的数据库数据进行分页的最佳方法是什么？

Question

I have been stuck with this problem for a while where I have to fetch data for a home page, like doing multiple queries to filter data by popularity, by most viewed etc. and merging all into a single query using "union". So the query orders them automatically by importance, for example featured goes first then most popular records go next and then most viewed and so on. The data changes from time to time if there is some new record, or some record becomes more popular than the other it might swap the order. However, when I fetch more data via a pagination or "load more", and at the same time some record swapped places with another one in the background, then this record would be shown again in the next page, which makes it redundant since it showed also on the first.

I checked out some twitter API algorithms with since_ID and max_ID, but in my case they don't help since I don't sort them by ID or any specific order, and this is where the complexity arises.

So how exactly am I supposed to deal with redundant data in this case? Has anyone ever had similar experiences?

Thanks in advance!

Answer 1

When you "load more", you can send the set of ID that is already displayed and consequently exclude them with an additionnal condition in your sql queries " and id not in ([excluded set here]) ".

However with a pagination system, it gets too complex since you have to pass the set of all page visited and you don't control the order of visit, it would turn into a complete mess. So with pagination I would recommend you simply let your ranking be and eventually cache it for X minutes. So all the users experience the same ranking for a few minutes, and pages never show duplicate content. Also, to improve user experience you can add a visual feedback when the ranking is updated, which provide a sense of interactivity.