Let's assume I have got a table with these 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:
I want to paginate it with 2 items per page and don't want to use
select id, createdAt from post order by createdAt desc, id desc limit 2
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
I can go on like this. Get the last item's
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
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.