1.. Licensed under the Apache License, Version 2.0 (the "License"); you may not 2.. use this file except in compliance with the License. You may obtain a copy of 3.. the License at 4.. 5.. http://www.apache.org/licenses/LICENSE-2.0 6.. 7.. Unless required by applicable law or agreed to in writing, software 8.. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT 9.. WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the 10.. License for the specific language governing permissions and limitations under 11.. the License. 12 13.. _views/pagination: 14 15================= 16Pagination Recipe 17================= 18 19This recipe explains how to paginate over view results. 20Pagination is a user interface (UI) pattern that allows the display of a 21large number of rows (`the result set`) without loading all the rows into the 22UI at once. A fixed-size subset, the `page`, is displayed along with next and 23previous links or buttons that can move the `viewport` over the result set to 24an adjacent page. 25 26We assume you’re familiar with creating and querying documents and views as 27well as the multiple view query options. 28 29Example Data 30============ 31 32To have some data to work with, we’ll create a list of bands, 33one document per band:: 34 35 { "name":"Biffy Clyro" } 36 37 { "name":"Foo Fighters" } 38 39 { "name":"Tool" } 40 41 { "name":"Nirvana" } 42 43 { "name":"Helmet" } 44 45 { "name":"Tenacious D" } 46 47 { "name":"Future of the Left" } 48 49 { "name":"A Perfect Circle" } 50 51 { "name":"Silverchair" } 52 53 { "name":"Queens of the Stone Age" } 54 55 { "name":"Kerub" } 56 57A View 58======= 59 60We need a simple map function that gives us an alphabetical list of band 61names. This should be easy, but we’re adding extra smarts to filter out “The” 62and “A” in front of band names to put them into the right position: 63 64.. code-block:: javascript 65 66 function(doc) { 67 if(doc.name) { 68 var name = doc.name.replace(/^(A|The) /, ""); 69 emit(name, null); 70 } 71 } 72 73The views result is an alphabetical list of band names. Now say we want to 74display band names five at a time and have a link pointing to the next five 75names that make up one page, and a link for the previous five, 76if we’re not on the first page. 77 78We learned how to use the ``startkey``, ``limit``, and ``skip`` parameters in 79earlier documents. We’ll use these again here. First, let’s have a look at 80the full result set: 81 82.. code-block:: javascript 83 84 {"total_rows":11,"offset":0,"rows":[ 85 {"id":"a0746072bba60a62b01209f467ca4fe2","key":"Biffy Clyro","value":null}, 86 {"id":"b47d82284969f10cd1b6ea460ad62d00","key":"Foo Fighters","value":null}, 87 {"id":"45ccde324611f86ad4932555dea7fce0","key":"Tenacious D","value":null}, 88 {"id":"d7ab24bb3489a9010c7d1a2087a4a9e4","key":"Future of the Left","value":null}, 89 {"id":"ad2f85ef87f5a9a65db5b3a75a03cd82","key":"Helmet","value":null}, 90 {"id":"a2f31cfa68118a6ae9d35444fcb1a3cf","key":"Nirvana","value":null}, 91 {"id":"67373171d0f626b811bdc34e92e77901","key":"Kerub","value":null}, 92 {"id":"3e1b84630c384f6aef1a5c50a81e4a34","key":"Perfect Circle","value":null}, 93 {"id":"84a371a7b8414237fad1b6aaf68cd16a","key":"Queens of the Stone Age","value":null}, 94 {"id":"dcdaf08242a4be7da1a36e25f4f0b022","key":"Silverchair","value":null}, 95 {"id":"fd590d4ad53771db47b0406054f02243","key":"Tool","value":null} 96 ]} 97 98Setup 99===== 100 101The mechanics of paging are very simple: 102 103- Display first page 104- If there are more rows to show, show next link 105- Draw subsequent page 106- If this is not the first page, show a previous link 107- If there are more rows to show, show next link 108 109Or in a pseudo-JavaScript snippet: 110 111.. code-block:: javascript 112 113 var result = new Result(); 114 var page = result.getPage(); 115 116 page.display(); 117 118 if(result.hasPrev()) { 119 page.display_link('prev'); 120 } 121 122 if(result.hasNext()) { 123 page.display_link('next'); 124 } 125 126Paging 127====== 128 129To get the first five rows from the view result, you use the ``?limit=5`` 130query parameter:: 131 132 curl -X GET http://127.0.0.1:5984/artists/_design/artists/_view/by-name?limit=5 133 134The result: 135 136.. code-block:: javascript 137 138 {"total_rows":11,"offset":0,"rows":[ 139 {"id":"a0746072bba60a62b01209f467ca4fe2","key":"Biffy Clyro","value":null}, 140 {"id":"b47d82284969f10cd1b6ea460ad62d00","key":"Foo Fighters","value":null}, 141 {"id":"45ccde324611f86ad4932555dea7fce0","key":"Tenacious D","value":null}, 142 {"id":"d7ab24bb3489a9010c7d1a2087a4a9e4","key":"Future of the Left","value":null}, 143 {"id":"ad2f85ef87f5a9a65db5b3a75a03cd82","key":"Helmet","value":null} 144 ]} 145 146By comparing the ``total_rows`` value to our ``limit`` value, 147we can determine if there are more pages to display. We also know by the 148`offset` member that we are on the first page. We can calculate the value for 149``skip=`` to get the results for the next page: 150 151.. code-block:: javascript 152 153 var rows_per_page = 5; 154 var page = (offset / rows_per_page) + 1; // == 1 155 var skip = page * rows_per_page; // == 5 for the first page, 10 for the second ... 156 157So we query CouchDB with:: 158 159 curl -X GET 'http://127.0.0.1:5984/artists/_design/artists/_view/by-name?limit=5&skip=5' 160 161Note we have to use ``'`` (single quotes) to escape the ``&`` character that is 162special to the shell we execute curl in. 163 164The result: 165 166.. code-block:: javascript 167 168 {"total_rows":11,"offset":5,"rows":[ 169 {"id":"a2f31cfa68118a6ae9d35444fcb1a3cf","key":"Nirvana","value":null}, 170 {"id":"67373171d0f626b811bdc34e92e77901","key":"Kerub","value":null}, 171 {"id":"3e1b84630c384f6aef1a5c50a81e4a34","key":"Perfect Circle","value":null}, 172 {"id":"84a371a7b8414237fad1b6aaf68cd16a","key":"Queens of the Stone Age", 173 "value":null}, 174 {"id":"dcdaf08242a4be7da1a36e25f4f0b022","key":"Silverchair","value":null} 175 ]} 176 177Implementing the ``hasPrev()`` and ``hasNext()`` method is pretty 178straightforward: 179 180.. code-block:: javascript 181 182 function hasPrev() 183 { 184 return page > 1; 185 } 186 187 function hasNext() 188 { 189 var last_page = Math.floor(total_rows / rows_per_page) + 190 (total_rows % rows_per_page); 191 return page != last_page; 192 } 193 194Paging (Alternate Method) 195========================= 196 197The method described above performed poorly with large skip values until 198CouchDB 1.2. Additionally, some use cases may call for the following 199alternate method even with newer versions of CouchDB. One such case is when 200duplicate results should be prevented. Using skip alone it is possible for 201new documents to be inserted during pagination which could change the offset 202of the start of the subsequent page. 203 204A correct solution is not much harder. Instead of slicing the result set 205into equally sized pages, we look at 10 rows at a time and use ``startkey`` to 206jump to the next 10 rows. We even use skip, but only with the value 1. 207 208Here is how it works: 209 210- Request `rows_per_page + 1` rows from the view 211- Display `rows_per_page` rows, `store + 1` row as ``next_startkey`` and 212 ``next_startkey_docid`` 213- As page information, keep ``startkey`` and ``next_startkey`` 214- Use the ``next_*`` values to create the next link, and use the others to 215 create the previous link 216 217The trick to finding the next page is pretty simple. Instead of requesting 10 218rows for a page, you request 11 rows, but display only 10 and use the values 219in the 11th row as the ``startkey`` for the next page. Populating the link to 220the previous page is as simple as carrying the current ``startkey`` over to the 221next page. If there’s no previous ``startkey``, we are on the first page. We 222stop displaying the link to the next page if we get `rows_per_page` or less 223rows back. This is called linked list pagination, as we go from page to 224page, or list item to list item, instead of jumping directly to a 225pre-computed page. There is one caveat, though. Can you spot it? 226 227CouchDB view keys do not have to be unique; you can have multiple index 228entries read. What if you have more index entries for a key than rows that 229should be on a page? ``startkey`` jumps to the first row, and you’d be screwed 230if CouchDB didn’t have an additional parameter for you to use. All view keys 231with the same value are internally sorted by `docid`, that is, the ID of 232the document that created that view row. You can use the ``startkey_docid`` 233and ``endkey_docid`` parameters to get subsets of these rows. For 234pagination, we still don’t need ``endkey_docid``, but ``startkey_docid`` is very 235handy. In addition to ``startkey`` and ``limit``, you also use 236``startkey_docid`` for pagination if, and only if, the extra row you fetch to 237find the next page has the same key as the current ``startkey``. 238 239It is important to note that the ``*_docid`` parameters only work in addition to 240the ``*key`` parameters and are only useful to further narrow down the result set 241of a view for a single key. They do not work on their own (the one exception 242being the built-in :ref:`_all_docs view <api/db/all_docs>` that already sorts 243by document ID). 244 245The advantage of this approach is that all the key operations can be 246performed on the super-fast B-tree index behind the view. Looking up a page 247doesn’t include scanning through hundreds and thousands of rows unnecessarily. 248 249Jump to Page 250============ 251 252One drawback of the linked list style pagination is that you can’t 253pre-compute the rows for a particular page from the page number and the rows 254per page. Jumping to a specific page doesn’t really work. Our gut reaction, 255if that concern is raised, is, “Not even Google is doing that!” and we tend 256to get away with it. Google always pretends on the first page to find 10 more 257pages of results. Only if you click on the second page (something very few 258people actually do) might Google display a reduced set of pages. If you page 259through the results, you get links for the previous and next 10 pages, 260but no more. Pre-computing the necessary ``startkey`` and ``startkey_docid`` 261for 20 pages is a feasible operation and a pragmatic optimization to know the 262rows for every page in a result set that is potentially tens of thousands 263of rows long, or more. 264 265If you really do need to jump to a page over the full range of documents (we 266have seen applications that require that), you can still maintain an integer 267value index as the view index and take a hybrid approach at solving pagination. 268