CouchDB and Pagination
It's taken me a while to figure out how to implement pagination with CouchDB but after trawling the interweb I finally posted my question on the couch-db user group and got the reply I needed (Thanks Paul Carey!). I have some results which need to be paginated
First Page Query:
-----------------
/my_db/_view/document/all
?count=25
&startkey_docid=null
&group=true
&endkey=["5455a49f69aeb7e9e6573159d4dfbca9",{}]
&descending=false
&startkey=["5455a49f69aeb7e9e6573159d4dfbca9",null] Next Pages Query:
-----------------
/my_db/_view/document/all
?count=25
&startkey_docid=e2a9572739696a257cc2fb25a7f6e495
&group=true
&endkey=["5455a49f69aeb7e9e6573159d4dfbca9",{}]
&descending=false
&startkey=["5455a49f69aeb7e9e6573159d4dfbca9",
"e2a9572739696a257cc2fb25a7f6e495"] Previous Pages Query:
---------------------
/my_db/_view/document/all
?count=25
&startkey_docid=e2a9572739696a257cc2fb25a7f6e495
&group=true
&endkey=["5455a49f69aeb7e9e6573159d4dfbca9",null]
&descending=true
&startkey=["5455a49f69aeb7e9e6573159d4dfbca9",
"e2a9572739696a257cc2fb25a7f6e495"]
Spot the differences between Previous and Next? To list results in reverse order in couch, you set 'descending' to true. I'd seen this in the pagination Futon, but I was still was getting unexpected results. Paul's reply fixed this though: I hadn't changed to endkey to refer to the start of my results (using null instead of {} in the second part of my composite key). The only thing left to do was reverse my results when descending using Array#reverse! Two things I still want to implement. I don't want the show the 'Next' link when I'm on the last page. I'd prefer to use numbered links alá "1 2 3 4 … 25" instead of "Previous/Next".
3 comments
Yeah - the offset feature, as you mention is supposedly only useful
for small offsets. I'm guessing it's not a good idea for pagination.
Another way of getting the page number would be with a reduce on the
map function which spits out every 20th (or however many results you
want to show per page) key. The advantage here being that you could
then use the result for the start keys.
2008/11/17
I know we're well outside the bounds of relational-land, but limit/offset is a concise model that would work nicely on all views, reduce or no. I know you need a solution *now*, but I'm optimistic that at some point an efficient offset will work its way into trunk -- if seeking to startkey_docid is possible, I can't imagine a seek to nth record without a full scan is intractable (perhaps I should learn Erlang and put my money where my mouth is). But it's just another data point for the index.
As for numbered links, you can obviously get your total pages by dividing total keys by your count. There's an offset in the view api, so you can really drop startkeys and endkeys entirely, just using offset and count, but if your set is quite large, you're going to run into some efficiency problems getting to the tail end of it, and you're back to using startkey.
One thing I can't figure out with the startkey/endkey method though is what if you have a huge number of records with the exact same key -- how do you do the pagination then? Wouldn't a limit/offset model be better (if offset were optimized to jump the index efficiently)?