Sorting CouchDB result by unix timestamp and paginate

Question

I am struggeling to get pagination working for a few days now. I have a database with docs and have a few to have the timestamp as key to sort descending. But I can't seem to get the next set of docs ...

If I run it, I get the top 5 docs. When I try to use startkey or startkey_docid I only seem to get the same lines again.

Trying the couch documentation I am not sure what I need to make it work.

couchdb has a design like:

{
  "_id": "_design/filters",
  "views": {
    "blog": {
      "map": "function (doc) { if (doc.published && doc.type == 'post') emit(doc.header.date); }"
    }
  }
}

... header.date is generated with +new Date()

on the nodejs side, with github/nano, I use something similar to:

import nano from 'nano';

let db = nano(SERVICE_URL).use('blog_main');

let lastDocId = ctx.query.lastDocId;
let lastSkip = ctx.query.lastSkip ? +ctx.query.lastSkip + 5 : null;

let query = {
    limit: 1 + 4,       // limit to 5
    descending: true,   // reverse order: newest to top
    include_docs: true,
}
if (lastDocId) { // initally off
    query.startkey = lastDocId;
}
if (lastSkip) { // other method for tests
    query.skip = lastSkip;  //  ----> this results in some previous and some new items
}

let itemRows = await db.view('filters','blog', query);
let items = itemRows.rows;
// each doc is in items[].doc

I have seen sort by value, sorting works for me - but I cant seem to get pagination to work.

RamblinRose · Answer 1 · 2020-01-26T16:19:41.637

I'm uncertain regarding the statement "I get the same lines again". That is reproducible if startkey is the first rather than the last key of the prior result - and that would be the first problem.

Regardless, assuming startkey is correct the parameters skip and startkey are conflicting. Initially skip should be 0 and afterwards it should be 1 in order to skip over startkey in successive queries.

This technique is clearly outlined in the CouchDB pagination documentation¹.

Details

Assume the complete view (where key is a unix timestamp) is

{
  "total_rows":7,
  "offset":0,
  "rows":[
    {"id":"821985c5140ca583e108653fb6091ac8","key":1580050872331,"value":null},
    {"id":"821985c5140ca583e108653fb6092c3b","key":1580050872332,"value":null},
    {"id":"821985c5140ca583e108653fb6093f47","key":1580050872333,"value":null},
    {"id":"821985c5140ca583e108653fb6094309","key":1580050872334,"value":null},
    {"id":"821985c5140ca583e108653fb6094463","key":1580050872335,"value":null},
    {"id":"821985c5140ca583e108653fb60945f4","key":1580050872336,"value":null},
    {"id":"821985c5140ca583e108653fb60949f3","key":1580050872339,"value":null}
  ]
}

Given the initial query conditions

{
    limit: 5,
    descending: true,
    include_docs: false // for brevity
}

indeed produces the expected result, 5 rows with the most recent first

{
  "total_rows":7,
  "offset":0,
  "rows":[
    {"id":"821985c5140ca583e108653fb60949f3","key":1580050872339,"value":null},
    {"id":"821985c5140ca583e108653fb60945f4","key":1580050872336,"value":null},
    {"id":"821985c5140ca583e108653fb6094463","key":1580050872335,"value":null},
    {"id":"821985c5140ca583e108653fb6094309","key":1580050872334,"value":null},
    {"id":"821985c5140ca583e108653fb6093f47","key":1580050872333,"value":null}
  ]
}

Now assuming the second query is so

{
    limit: 5,
    descending: true,
    include_docs: false, // for brevity
    startkey: 1580050872333,
    skip: 5
}

startkey (the key of the last row of the prior result) is correct but the skip parameter is literally skipping past the next (logical) set of rows. Specifically with those parameters and the example view above, the query would blow past the remaining keys resulting in an empty row set.

This is what is desired:

{
    limit: 5,
    descending: true,
    include_docs: false, // for brevity
    startkey: 1580050872333,
    skip: 1 // just skip the last doc (startkey)
}

1 _{CouchDB Pagination Recipes 3.2.5.5. Paging (Alternate Method)}

Thanks for your effort. Either filtering did not work, due to having similar keys. Result became unpredictable when grabbing portions of the result set. Seems to have triggered a bug in my specific case. — BananaAcid, Jan 28 '20 at 07:04

score 0 · Accepted Answer · answered Jan 28 '20 at 07:01

Using startkey or skip, returned results that included some of the skipped results as well, or all previous ones (strangely mixed up).

I solved it, by extending the result keys with - second part. Since the key was based on a date without time, it seemed to have rearranged the entries on each request due to similar date-timestamps. Adding a second part that was was sortable as well (used the created timestamp as second part) fixed it. The key is now [datetimestamp, createdtimestamp] .. both can be sorted descending.

Sorting CouchDB result by unix timestamp and paginate

2 Answers2