Join/sum with CouchDB

Question

Using CouchDB, I currently have a document which represents an idea, you can rate this idea. Every idea is one document and every rating is a different document. I am doing this like this to avoid concurrent access problems when people are rating an idea.

My documents look like that (I have simplified them):

An idea:

{
 "_id": "idea1",
 "title": "A great idea"
}

Ratings:

{
 "_id": "rating1",
 "rating": 1,
 "author": "author1"
}

{
 "_id": "rating2",
 "rating": 1,
 "author": "author2"
}

I currently use a reduce function to return me the idea id and his/her rating (a simple sum of the ratings):

Map:

function(doc) {
  if (doc.type == "rating")
    emit(doc.idea_id, doc.rating);    
}

Reduce:

function(keys, values, rereduce) {
  return sum(values);
}

My question is: how could I "join" the "idea" document with the reduce result which represent the rating for the idea?

Answer 1

First off, please don't create your own _id, let CouchDB create a uuid for you. It's much better, I promise you :)

The short answer is, you can't get the idea document with anything other than an additional query. Although the query is quite fast since you have the _id in your vote document.

If you wanted to return the the full documents for all of the votes, in order to grab comments or something, you could definitely do that. Just run the view query with ?include_docs=true

Answer 2

I don't agree with server-side ID generation. Client-Side ID generation protects you from duplicate inserts. sha("%f@%s-%f" % (time(), hostname(), random()) for example generates very reasonable IDs.

Answer 3

Map:

function(doc) {
    switch (doc.type) {
        case "idea":   emit(doc._id,     ["idea",  doc]);         break;
        case "rating": emit(doc.idea_id, ["rating", doc.rating]); break;
    }
}

Reduce:

function(keys, values, rereduce) {
    var i, l, ret = {idea:null, rating:0};

    for (i = 0, l = values.length; i < l; ++i) {
        switch (values[i][0]) {
            case "idea":   ret.idea = values[i][1];    break;
            case "rating": ret.rating += values[i][1]; break;
        }
    }

    return ret;
}

Another option would be to use view collation to do the trick.

Answer 4

Seconding @mdornseif. UUIDs can be generated client side to avoid network errors. In case no UUID generation facility is at hand, CouchDB has a /_uuids endpoint to request one or more for later use.