How to populate Comment count to an Item list?

I have two models in my app: Item and Comment. An Item can have many Comments, and a Comment instance contains a reference to an Item instance with key 'comment', to keep track of the relationship.

Now I have to send a JSON list of all Items with their Comment count when user requests on a particular URL. 

function(req, res){
  return Item.find()
    .exec(function(err, items) {
        return res.send(items);
    });
};

I am not sure how can I "populate" comment count to the items. This seems to be a common problem and I tend to think there should be some nicer way of doing this job than brute force.

So please share your thoughts. How would you "populate" the Comment count to the Items?

check the MongoDB documentation and look for the method findAndModify() -- with it you can atomically update a document, e.g. add a comment and increment the document counter at the same time.

findAndModify

The findAndModify command atomically modifies and returns a single document. By default, the returned document does not include the modifications made on the update. To return the document with the modifications made on the update, use the new option.

Example

Use the update option, with update operators $inc for the counter, and $addToSet for adding the actual comment to an embedded array of comments.

 db.runCommand(
            {
              findAndModify: "item",
              query: { name: "MyItem", state: "active", rating: { $gt: 10 } },
              sort: { rating: 1 },
              update: { $inc: { commentCount: 1 }, 
                        $addToSet: {comments: new_comment}  }
            }
              )

See:

MongoDB: findAndModify

MongoDB: Update Operators

I did some research on this issue and came up with following results. First, MongoDB docs suggest:

In general, use embedded data models when:

  • you have “contains” relationships between entities.
  • you have one-to-many relationships where the “many” objects always appear with or are viewed in the context of their parent documents.

So in my situation, it makes much more sense if Comments are embedded into Items, instead of having independent existence.

Nevertheless, I was curious to know the solution without changing my data model. As mentioned in MongoDB docs:

Referencing provides more flexibility than embedding; however, to resolve the references, client-side applications must issue follow-up queries. In other words, using references requires more roundtrips to the server.

As multiple roundtrips are kosher now, I came up with following solution:

var showList = function(req, res){

  // first DB roundtrip: fetch all items
  return Item.find()
        .exec(function(err, items) {
            // second DB roundtrip: fetch comment counts grouped by item ids
            Comment.aggregate({
                $group: {
                    _id: '$item',
                    count: {
                        $sum: 1
                    }
                }
            }, function(err, agg){
                // iterate over comment count groups (yes, that little dash is underscore.js)
                _.each(agg, function( itr ){
                    // for each aggregated group, search for corresponding item and put commentCount in it
                    var item = _.find(items, function( item ){
                        return item._id.toString() == itr._id.toString();
                    });
                    if ( item ) {
                        item.set('commentCount', itr.count);
                    }
                });
                // send items to the client in JSON format
                return res.send(items);
            })          
        });

};

Agree? Disagree? Please enlighten me with your comments!

If you have a better answer, please post here, I'll accept it if I find it worthy.