CouchDB sorting on a group of complex keys

Question

I am having hard time finding out how to query on multiple keys and sorting by date. In fact I would like to translate the following SQL :

SELECT * FROM THREADS WHERE GROUP_ID IN (1,2,3) ORDER BY THREAD.DATE;

So far I was passing in the query keys=[1,2,3] This returns the threads but they are not sorted.

I could also sort them but only on one GROUP_ID (having as key :[group_id, date]): startkey=[GROUP_ID,{}]&endkey=[GROUP_ID]&descending=true

Yet, I couldn't combine it.

Anyone know a solution ?

Thanks a lot,

Patrick

score 0 · Answer 1 · answered Mar 17 '12 at 09:39

I simply can't pass Patrick's question by in a such a day :) Though there is no simple solution to your relational problem, you may try this workaround using list design (couchDB >= 0.10):

{
  "_id": "_design/test",
  "views": {
    "by_date": {
      "map": "function(doc) {
        emit(doc.date, doc);
      }"
    }
  },
  "lists" : {
    "filter_groups" : "function(head, req) { 
      start({
        'headers': {
          'Content-Type': 'application/json'
        }
      });
      var groups = JSON.parse(req.query['groups'] || '[]');
      var row;
      var res = {rows:[]};
      while (row = getRow()) {
        if (groups.indexOf(row.value.group_id) > -1) {
          res.rows.push(row);
        }
      } 
      send(JSON.stringify(res));
    }",
  }
}

You query it this way: curl -g $DB/_design/test/_list/filter_groups/by_date?groups=[1,3] , passing an array of group_id's needed.

CouchDB sorting on a group of complex keys

1 Answers1