MySQL – Pagination Without OFFSET

MySQLpaging

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 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.

Best Answer

Here's one way:

mysql> #select all in reverse
mysql> select id, createdAt from post order by createdAt desc, id desc;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   16 | 2014-11-16 09:11:18 |
|   15 | 2014-11-16 09:11:03 |
|   14 | 2014-11-16 09:10:32 |
|   13 | 2014-11-16 09:10:05 |
|   12 | 2014-11-16 09:09:47 |
|   11 | 2014-11-13 18:35:01 |
|   10 | 2014-11-13 18:33:08 |
|    9 | 2014-11-13 18:33:01 |
|    8 | 2014-11-13 17:51:21 |
|    7 | 2014-11-13 17:40:30 |
|    6 | 2014-11-12 18:37:44 |
|    5 | 2014-11-12 18:21:44 |
|    4 | 2014-11-12 18:17:30 |
|    3 | 2014-11-12 17:31:31 |
|    2 | 2014-11-11 18:37:40 |
|    1 | 2014-11-11 17:28:41 |
+------+---------------------+
16 rows in set (0.01 sec)

mysql> #select last two
mysql> select id, createdAt from post order by createdAt desc, id desc limit 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   16 | 2014-11-16 09:11:18 |
|   15 | 2014-11-16 09:11:03 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #select next button
mysql> 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;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   14 | 2014-11-16 09:10:32 |
|   13 | 2014-11-16 09:10:05 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #select next button again
mysql> SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:10:05' AND (id < '13' OR createdAt < '2014-11-16 09:10:05') ORDER BY createdAt DESC, id DESC LIMIT 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   12 | 2014-11-16 09:09:47 |
|   11 | 2014-11-13 18:35:01 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #undo next button (just to get back conceptually)
mysql> 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;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   14 | 2014-11-16 09:10:32 |
|   13 | 2014-11-16 09:10:05 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #we already saw 'next' from here
mysql> #now let's click the previous button
mysql> #change the direction of the inequalities and order by statement
mysql> SELECT id, createdAt FROM post  WHERE createdAt >= '2014-11-16 09:10:32' AND (id > 14 OR createdAt > '2014-11-16 09:10:32') ORDER BY createdAt asc, id asc LIMIT 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   15 | 2014-11-16 09:11:03 |
|   16 | 2014-11-16 09:11:18 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #and if you want them sorted in desc:
mysql> select id, createdAt from (SELECT id, createdAt FROM post  WHERE createdAt >= '2014-11-16 09:10:32' AND (id > 14 OR createdAt > '2014-11-16 09:10:32') ORDER BY createdAt asc, id asc LIMIT 2 ) a order by createdAt desc, id desc;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   16 | 2014-11-16 09:11:18 |
|   15 | 2014-11-16 09:11:03 |
+------+---------------------+
2 rows in set (0.00 sec)
Related Question