Select top/latest 10 in couchdb?

Question

How would I execute a query equivalent to "select top 10" in couch db?

For example I have a "schema" like so:

title   body   modified

and I want to select the last 10 modified documents.

As an added bonus if anyone can come up with a way to do the same only per category. So for:

title   category   body   modified

return a list of latest 10 documents in each category.

I am just wondering if such a query is possible in couchdb.

Answer 1

To get the first 10 documents from your db you can use the limit query option. E.g. calling

http://localhost:5984/yourdb/_design/design_doc/_view/view_name?limit=10

You get the first 10 documents.

View rows are sorted by the key; adding descending=true in the querystring will reverse their order. You can also emit only the documents you are interested using again the querystring to select the keys you are interested. So in your view you write your map function like:

function(doc) {
    emit([doc.category, doc.modified], doc);
}

And you query it like this:

http://localhost:5984/yourdb/_design/design_doc/_view/view_name?startkey=["youcategory"]&endkey=["youcategory", date_in_the_future]&limit=10&descending=true

Answer 2

here is what you need to do.

Map function

function(doc)
{
  if (doc.category)
  {
    emit(['category', doc.category], doc.modified);
  }
}

then you need a list function that groups them, you might be temped to abuse a reduce and do this, but it will probably throw errors because of not reducing fast enough with large sets of data.

function(head, req)
{
  % this sort function assumes that modifed is a number
  % and it sorts in descending order
  function sortCategory(a,b) { b.value - a.value; }
  var categories = {};
  var category;
  var id;
  var row;
  while (row = getRow())
  {
    if (!categories[row.key[0]])
    {
      categories[row.key[0]] = [];
    }
    categories[row.key[0]].push(row);
  }
  for (var cat in categories)
  {
    categories[cat].sort(sortCategory);
    categories[cat] = categories[cat].slice(0,10);
  }
  send(toJSON(categories));  
}

you can get all categories top 10 now with

http://localhost:5984/database/_design/doc/_list/top_ten/by_categories

and get the docs with

http://localhost:5984/database/_design/doc/_list/top_ten/by_categories?include_docs=true

now you can query this with a multiple range POST and limit which categories

curl -X POST http://localhost:5984/database/_design/doc/_list/top_ten/by_categories -d '{"keys":[["category1"],["category2",["category3"]]}'

you could also not hard code the 10 and pass the number in through the req variable.

Here is some more View/List trickery.