CouchDB view collation sorted by date

Question

I am using a couchDB database.

I can get all documents by category and paginate results with a key like ["category","document_id"]and a query likestartkey=["category","document_id"]&endkey=["category",{}]`

Now I want to sort those results by date to have latest documents first.

I tried a lot of keys such as ["category","date","document_id"] but nothing works (or I can't get it working).

I would use something like startkey=["queried_category","queried_date","queried_document_id"]&endkey=["queried_category"]

but ignore the "queried_date" key part (sort but do not take documents where "document_id" > "queried_document_id")

EDIT: Example : With a key like : startkey=["apple","2012-12-27","ZZZ"]&endkey=["apple",{}]&descending=true

I will have (and it is the normal behavior)

"apple","2012-12-27","ABC"
"apple","2012-05-01","EFG"
 ...
"apple","2012-02-13","ZZZ"
...

But the result set I want should start with "apple","2012-02-13","ZZZ"

score 0 · Answer 1 · answered Dec 28 '12 at 19:45

0

Emit the category and the timestamp (you don't need the document_id):

emit(category, timestamp);

And then filter on the category:

?startkey=[":category"]&endkey=[":category",{}]

You must understand that this is only a sort, so you need the startkey to be before the first row, and the endkey to be after the last row.

Last but not least, don't forget to have a representation for the timestamp that is adequate to the sort.

answered Dec 28 '12 at 19:45

Aurélien Bénel

3,775
24
45

how can I paginate results without a document ID? – MiniScalope Dec 29 '12 at 01:24
1

You can paginate with the `skip` and `limit` [query parameters](http://wiki.apache.org/couchdb/HTTP_view_API#Querying_Options) – Dominic Barnes Dec 29 '12 at 02:33
I first used the ID because it is said everywhere the skip function is very inefficient and not recommended. – MiniScalope Dec 29 '12 at 02:47
The developer mantra is "Do it simple first then, when it works, optimize what appeared to be a bottleneck". – Aurélien Bénel Dec 29 '12 at 08:54
But I like to follow best practices when possible. When a vendor says "do not use" I try to avoid – MiniScalope Dec 29 '12 at 13:30
You have a good discussion on this on [StackOverflow](http://stackoverflow.com/questions/312163/pagination-in-couchdb). The solution is to get one more row, and use its key as the startkey. It doesn't need the document ID to be in the key. Of course id you don't want the same rows to appear on different pages, you will have to use more detailed timestamps than in your example. – Aurélien Bénel Dec 29 '12 at 13:53

score 0 · Answer 2 · answered Dec 29 '12 at 11:41

The problem with pagination with timestamp instead of doc ID is that timestamp is not unique. That's why you will have problem with paging Aurélien's solution.

I would stay with what you tried but use timestamp as the number (standard UNIX milliseconds since 1970). You can reverse the order of single numeric field just by multiplying by -1:

emit(category, -timestamp, doc_id)

This way result sorted lexicographically (ascending) will be ordered according to your needs:

first dates descending,
then document id's ascending.

CouchDB view collation sorted by date

2 Answers2