CODEWITHSUNDEEP
mysqlsqlsql-serverpostgresqlpagination
Mithun Sreedharan
Mithun Sreedharan

Reputation: 51272

Pagination issue while sorting based on last modified property

I need to show some records sorted based on modified column (latest activity on top) (Post with new edit or comments at the top)

App UI has twitter like 'more' post button for infinite scroll. each 'more' will add next 10 records to UI.

Issue is that pagination index breaks when any of the to be shown record is modified

for example Suppose i have records A,B,C,..Z in jobs table.

first time I'm' showing the records A-J to the user using

SELECT * FROM Jobs WHERE 1 ORDER BY last_modified DESC LIMIT 0, 10

second time if none of the records are modified 

SELECT * FROM Jobs WHERE 1 ORDER BY last_modified DESC LIMIT 10, 10

will return K-T

But if some body modifies any records after J before the user clicks 'more button',

SELECT * FROM Jobs WHERE 1 ORDER BY last_modified DESC LIMIT 10, 10

will return J-S

Here record J is duplicated. I can hide it by not inserting J to the UI, but the more button will show only 9 records. But this mechanism fails when large number of records are updated, If 10 records are modified, the query will return A-J again.

What is the best way to handle this pagination issue?

Keeping a second time stamp fails if a record has multiple updates.

Server cache of queries?

Upvotes: 3

Views: 622

Answers (3)

Ankit Pundhir
Ankit Pundhir

Reputation: 1095

I don't know the exact solution but I can give it a try.

First u need an integer ID column in your Job table. Now send a max_id = null along with limit = 10 and offset = 0 from UI.

In this case if max_id is null, set max_id to (MAX(ID) + 1) of Table.

SELECT (MAX(ID) + 1) INTO max_id FROM Jobs;

Later find the records:

SELECT * FROM Jobs WHERE ID < max_id ORDER BY last_modified DESC LIMIT 10 OFFSET 0;

Return the records to UI.

Now from UI set max_id = ID of first record in the response array, offset = offset + limit.

Now onwards try with updated values of max_id and offset:

SELECT * FROM Jobs WHERE ID < max_id ORDER BY last_modified DESC LIMIT 10 OFFSET 10;

Upvotes: 0

Utku Yıldırım
Utku Yıldırım

Reputation: 2277

Twitter timelines not paged queries they are queried by ids

This page will help you a lot understanding timeline basics https://dev.twitter.com/docs/working-with-timelines

lets say each column have id field too

id msg
1  A
2  B
....

First query will give you 10 post and max post_id will be 10

Next query should be

SELECT * FROM Jobs WHERE id > 10 ORDER BY last_modified DESC LIMIT 0, 10

Upvotes: 1

Giles Wells
Giles Wells

Reputation: 128

I would do a NOT IN() and a LIMIT instead of just a straight LIMIT with a pre-set offset.

SELECT * FROM Jobs WHERE name NOT IN('A','B','C','D','E','F','G','H','I','J') 
ORDER BY last_modified DESC LIMIT 10

This way you still get the most recent 10 every time but you would need to be tracking what IDs have already been shown and constantly negative match on those in your sql query.

Upvotes: 2

Related Questions