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.

Personal

Is there a reason you don't want to do it manually? First query for the available categories, then query the lists per category. Doing the second query batched, that is almost like on query. – Middelburg

I can do that yes - but my question is whether something like this is Possible in couchdb, in one query. For example one can do this in sql with a really convoluted complex nested select statement. – Personal

Answer 1

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.

Endarch

I didn't test this answer, but I'll accept it anyways as I no longer work with couchdb. – Personal

Answer 2

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

Emmettemmey

Will that second query actually select latest 10 items in Each of the categories? Or just latest 10 items from One category? – Personal

Oops. I misread your question. It selects latest 10 items from one category. – Emmettemmey

Answer 3

slight correction. it was not sorting untill I added the "return" keyword in your sortCategory function. It should be like this:

function sortCategory(a,b) { return b.value - a.value; }

Azar