CouchDB querying and filtering by three indexed ke

Question

I'm currently trying to order and sort by a key with three values. But lets start with the document structure:

{
    _id: "DOCIDGOESHERE01",
    type: "MESSAGE",
    date: "2011-08-24 06:49:02",
    author: "USERIDGOESHERE01",
    receiver: ["USERIDGOESHERE02", "USERIDGOESHERE03"],
    message: "ok let's do this"
}

The main goal is to query couchDB for messages send by selected users to one specific user and order them by the date. Some messages don't have any receiver which indicates them to be public and can be read by anyone.

The map function i currently use looks like this:

function map(doc) {
    if(doc.receiver.lenth==0)
        emit([doc.date, null, doc.author], doc._id);
    else for(var idx in doc.receiver)
        emit([doc.date, doc.receiver[idx], doc.author], doc._id);
}

When querying the couchDB HTTP interface i tryed requests like

HTTP GET xxx/messages?key=[{}, "USERIDGOESHERE02", {}]

or

HTTP POST xxx/messages
{
    keys: [
        [{}, "USERIDGOESHERE02", "USERIDGOESHERE01"],
        [{}, "USERIDGOESHERE02", "USERIDGOESHERE03"],
        [{}, "USERIDGOESHERE02", "USERIDGOESHERE04"],
    ]
}

but all of them didn't result in the list of documents i wanted to produce. Do you have any suggestions with this task? Or is it impossible to build such filtered results with couchDB? Thank you very much in advance!

Answer 1

I think you want;

emit([doc.author, doc.receiver[idx], doc.date], null);

You can then query with

startkey=["USERID1","USERID2"]&endkey=["USERID1","USERID2",{}]

this will return all documents sent by USERID1 to USERID2 in date order. {} is an empty object and, by the rules of CouchDB collation, will sort higher than any number or string, hence the range here is guaranteed to include all possible dates.

Finally, I'll note that CouchDB does not support wildcards.

Answer 2

Keys are always sorted smallest to highest, in one long 1-dimensional list. (I have tried to describe this intuitively in The Parable of CouchDB but no idea if I succeeded!)

What does an array sorted smallest-to-largest look like? If you read all the keys in a view, the left-hand value varies the least; the middle value varies more than the left; and the right-hand value varies the most. In other words, array keys tell CouchDB, "First priority is to sort by key[0], if that is equal, the tie-breaker will be key[1]; if those are equal also, the next tiebreaker is key[2], etc..."

Therefore you probably want your keys to look like this:

[ "receiver_1", null      , a_date       ],
[ "receiver_1", "sender_A", some_date    ],
[ "receiver_1", "sender_B", another_date ],
[ "receiver_2", "sender_A", fourth_date  ],
[ "receiver_3", "sender_C", fifth_date   ],

To find all messages for receiver_1 from sender_B and also public messages, you need two queries, one for the "receiver_1", null pairings, and another for "receiver_1", "sender_B". You want to know any date, so you need a range of rows that match the sender/receiver. Unfortunately, the HTTP POST query does not support this.

You could simply query for each selected sender (even all at the same time using threads or asynchronous programming). The receiver and sender are known, and this example allows a range from the smallest value (null) to the largest ({}), which will include all the dates.

?startkey=["receiver_1",null,null]&endkey=["receiver_1",null,{}]
?startkey=["receiver_1","sender_B",null]&endkey=["receiver_1","sender_B",{}]

Another option is to simplify your keys and remove the dates.

[ "receiver_1", null      ],
[ "receiver_1", "sender_A"],
[ "receiver_1", "sender_B"],
[ "receiver_2", "sender_A"],
[ "receiver_3", "sender_C"],
[ "receiver_3", "sender_C"],
[ "receiver_3", "sender_C"],
[ "receiver_3", "sender_C"],
[ "receiver_3", "sender_C"],

Now you can query with the HTTP POST API again. Messages will return not ordered by date. This is not so bad, you can sort them on the client (or a _list function). And remember, even in my first example the dates are not perfectly sorted either.