CODEWITHSUNDEEP
mysqlsqlpagination
cnkt
cnkt

Reputation: 2973

Mysql pagination without offset

Let's assume I have got a table with these data:

Table data

I want to order it by createdAt column DESC, and id column DESC

select id, createdAt from post order by createdAt desc, id desc

Now it looks like this:

ORDER BY createdAt DESC, id DESC

I want to paginate it with 2 items per page and, for performance reasons, I don't want to use offset, just limit:

select id, createdAt from post order by createdAt desc, id desc limit 2

enter image description here

To get the next 2 items i use this query:

SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2

enter image description here

I can go on like this. Get the last item's createdAt and id then use it for next page query.

But i'm trying to formulate the previous page query for almost two days and can't figure out a way yet.

Here is what i tried already:

Get the first item from current result (instead of last) use it's id and createdAt field and reverse the conditions in query (createdAt >=, id >, created at >). But this query always gives me the first two results (which is normal because the rows providing this conditions are the first two).

I'm already out of ideas. I need help. Thanks.

Upvotes: 2

Views: 2474

Answers (1)

FrankPl
FrankPl

Reputation: 13315

You could order ascending to get the correct records:

SELECT id, createdAt 
  FROM post 
 WHERE createdAt >= '2014-11-16 09:11:03' 
   AND (id > '15' OR createdAt > '2014-11-16 09:11:03') ORDER BY createdAt ASC, id ASC
LIMIT 2

and the reverse the sorting when displaying the result set.

Upvotes: 4

Related Questions