{"id":46,"date":"2013-03-20T20:23:55","date_gmt":"2013-03-20T20:23:55","guid":{"rendered":"http:\/\/andrewpleasants.co.uk\/?p=46"},"modified":"2013-03-20T20:23:55","modified_gmt":"2013-03-20T20:23:55","slug":"nextprev-pagination-done-right","status":"publish","type":"post","link":"http:\/\/andrewpleasants.co.uk\/?p=46","title":{"rendered":"Next\/Prev Pagination Done Right"},"content":{"rendered":"

We all use pagination of some sort in our programs – but it turns out that the majority of us might be doing it wrong!<\/strong>
\n<\/p>\n

\nConsider the following Comments table:\n<\/p>\n\n\n\n\n\n
id<\/td>\nint
\n<\/td><\/tr>\n
post<\/i><\/td>\nint<\/td>\n<\/tr>\n
from<\/i><\/td>\nint<\/td>\n<\/tr>\n
comment<\/td>\nvarchar<\/td>\n<\/tr>\n\n\n<\/table>\n

\nIf you wanted to page the above table you’d probably do something like…\n<\/p>\n

\r\nSELECT * FROM Comments\r\nWHERE post = @postID\r\nORDER BY id DESC\r\nLIMIT 0 10;\r\n<\/pre>\n
And…<\/p>\n
\r\nSELECT * FROM Comments\r\nWHERE post = @postID\r\nORDER BY id DESC\r\nLIMIT 10 10;\r\n<\/pre>\n
\nFor the second page, and so on.  However, what happens if another user adds a comment before the second page is loaded?  You’d find the last comment on page 1 repeated at the top of page 2!  Or if a comment from page 1 is deleted, you’d miss a possibly important comment at the top of page 2.  I have actually noticed this behaviour myself in the YouTube app for Android.\n<\/p>\n
\nThe solution to this minor problem is quite simple; use the date field of the last comment as a start point (or pivot) for the limit statement:\n<\/p>\n
\r\nSELECT * FROM Comments\r\nWHERE post = @postID\r\nAND date < @startDate\r\nORDER BY id DESC\r\nLIMIT 10;\r\n<\/pre>\n
\nThe above statement will only select rows older than the last comment, and then take 10 of them.  The problem with the first method is that using the row offset in <\/p>\n
LIMIT 10 10<\/pre>\n
 is static, and the database table is not.  Using a relative value such as the timestamp allows you to effectively track the position of the row in the table<\/i>.\n<\/p>\n
But Beware!<\/h3>\n
\nThis is not a magic fix for all pagination (as the title suggests).  What happens if the user wants to jump to page 3?  Or the last page?  This method only works when the pivot point is setby the select statment.  By this I mean that you know the pivot point for the next prev\/next request when you make the select (it's the last row).\n<\/p>\n
\nIf however you only need to support next\/prev operations (such as in an infinitely scrolling list in mobile development) this method is ideal!\n<\/p>\n
Source: https:\/\/coderwall.com\/p\/lkcaag<\/a><\/h5>\n","protected":false},"excerpt":{"rendered":"
We all use pagination of some sort in our programs – but it turns out that the majority of us might be doing it wrong!<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24,23],"tags":[26,28,27,25],"class_list":["post-46","post","type-post","status-publish","format-standard","hentry","category-databases","category-programming","tag-pagination","tag-programming","tag-software-development","tag-sql-limit"],"_links":{"self":[{"href":"http:\/\/andrewpleasants.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/46","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/andrewpleasants.co.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/andrewpleasants.co.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/andrewpleasants.co.uk\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/andrewpleasants.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=46"}],"version-history":[{"count":6,"href":"http:\/\/andrewpleasants.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/46\/revisions"}],"predecessor-version":[{"id":52,"href":"http:\/\/andrewpleasants.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/46\/revisions\/52"}],"wp:attachment":[{"href":"http:\/\/andrewpleasants.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=46"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/andrewpleasants.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=46"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/andrewpleasants.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=46"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}