CouchDB sorting and filtering in the same view

Question

Hi, I'm trying to use CouchDB for a new app, and I need to create a view that sorts by multiple fields and also filters by multiple fields. Here is an example document, I've left out the _id and _rev to save myself some typing.

{
    "title": "My Document",
    "date": 1279816057,
    "ranking": 5,
    "category": "fun",
    "tags": [
        "couchdb",
        "technology"
    ],
}

From the documentation, I've learned that I can easily create a view that sorts by a field such as ranking.

function(doc) {
    emit(doc.ranking, doc);
}

I've also learned that I can easily filter by fields such as category

function(doc) {
    emit(doc.category, doc);
}

http://127.0.0.1:5984/database/_design/filter/_view/filter?key=%22fun%22

My problem is that I need to do a bunch of these things all at the same time. I want to filter based on category and also tag. I should be able to filter down to only documents with category of "fun" and tag of "couchdb". I want to sort those filtered results by ranking in descending order, then by date in ascending order, then by title in alphabetical order.

How can I create one view that does all of that sorting and filtering combined?

Answer 1

For emitting more than one piece of data in a key, you'll want to read up on Complex Keys http://wiki.apache.org/couchdb/View_collation You'll most likely end up emit()'ing a key that is an array made up of the category and tag. For example...

function(doc) {
  for(var i = 0; i < doc.tags.length; i++)
    emit([doc.category, doc.tags[i]], doc);
}

Now when you query ?key=["fun", "couchdb"] you'll get all the items in the fun category tagged as couchdb. Or if you want all of the items in the fun category, regardless of their tag, then you can query with a range: ?startkey=["fun"]&endkey=["fun", {}] Just remember, if your item has multiple tags that you'll get it multiple times in the results (because you emit()'d the doc once per tag).

To go the extra step of sorting by rating, date, and title you'll add two more elements to your array: an integer and either the ranking, date, or title. Remember, you can emit() more than once per map function. An example map function...

function(doc) {
  for(var i = 0; i < doc.tags.length; i++)
  {
     emit([doc.category, doc.tags[i], 0, doc.ranking], doc);
     emit([doc.category, doc.tags[i], 1, doc.title], doc);
     emit([doc.category, doc.tags[i], 2, doc.date], doc);
  }
}

Now your key structure is: ["category", "tag", 0 ... 2, rank/title/date]

You're basically grouping all of the rankings under 0, titles under 1, and dates under 2. Of course, you're transmitting a lot of date, so you could either break each of these groupings out into a separate view in your design document, or only return the doc's _id as the value (emit([ ...], doc._id);).

Get everything in the fun category with the couchdb category (ascending):

?startkey=["fun", "couchdb"]&endkey=["fun", "couchdb", {}, {}]

Get everything in the fun category with the couchdb category (descending):

?startkey=["fun", "couchdb", {}, {}]&endkey=["fun", "couchdb"]&descending=true

Get only rankings in the fun category with the couchdb tag (ascending):

?startkey=["fun", "couchdb", 0]&endkey=["fun", "couchdb", 0, {}]

Get only rankings in the fun category with the couchdb tag (descending):

?startkey=["fun", "couchdb", 0, {}]&endkey=["fun", "couchdb", 0]&descending=true

I hope this helps. Complex keys start to really show how powerful Map/Reduce is at slicing and dicing data.

Cheers.