Sorting records on number of embedded documents

Question

I am using mongodb and mongoose, And I have a situation where a user creates a rating and a review, what I want is to fetch review of the product whose review has the highest number of votes. Is it possible to do it in mongo?

The structure is

{ "product" : ObjectId("53ccfa53b502542b2f463acd"),
  "_id" : ObjectId("53e8675aea39355818ec4ab2"),
  "vote" : [ ],
  "review" : "Blah Blah Blah",
  "stars" : 3,
  "email" : "user@example.com", "__v" : 0 }

Now I want to show the review that has achieved the most votes, I know if after find() I put sort() and limit() function, it can be achieved through a field present on the same document level however I do not know how to handle multiple records in this case 'vote'....

Answer 1

The best thing you can do is to maintain a "voteCount" on the document itself. The reasons will become self apparent in a moment.

You can maintain this as members are added or removed from the array. Let's say you are using an ObjectId and the $push and $pull update operators to do this. So you also you $inc with a bit of query logic to make sure you don't duplicate the "User ObjectId" casting the vote. Assuming a model called "Product":

Product.update(
    {
        "_id": ObjectId("53e8675aea39355818ec4ab2"),
        "votes": { "$ne": ObjectId("53e87caaca37ffa384e5a931") }, // the user ObjectId
    },
    {
        "$push": { "votes": ObjectId("53e87caaca37ffa384e5a931" }, // same Id
        "$inc": { "voteCount": 1 }
    },
    function(err) {

    }
);

And to remove:

Product.update(
    {
        "_id": ObjectId("53e8675aea39355818ec4ab2"),
        "votes": ObjectId("53e87caaca37ffa384e5a931"), // the user ObjectId
    },
    {
        "$pull": { "votes": ObjectId("53e87caaca37ffa384e5a931" }, // same Id
        "$inc": { "voteCount": -1 }
    },
    function(err) {

    }
);

Then it's just a matter of sorting on the field:

Product.find().sort({ "voteCount": -1 }).limit(1).exec(function(err,doc) {


});

But if for some reason you cannot see fit to keep the "voteCount" in the document, then you need to manually "project" this with the aggregation framework. Etiher using the $size aggregate method where you have MongoDB 2.6 or greater:

Product.aggregate(
    [
        { "$project": {
            "product": 1,
            "vote": 1,
            "review": 1,
            "stars": 1,
            "email": 1,
            "voteCount": { "$size": "$vote" }
        }},
        { "$sort": { "voteCount": -1 } },
        { "$limit": 1 }
    ],
    function(err,result) {

    }
);

Or by $unwind on the array and getting the count via $sum for earlier versions:

Product.aggregate(
    [
        { "$unwind": "$vote"
        { "$group": {
            "_id": "$_id",
            "product": { "$first": "$product" },
            "vote": { "$push": "$vote" },
            "review": { "$first": "$review" },
            "stars": { "$first": "$stars" },
            "email": { "$first": "$email" },
            "voteCount": { "$sum": 1 }
        }},
        { "$sort": { "voteCount": -1 } },
        { "$limit": 1 }
    ],
    function(err,result) {

    }
);

The aggregation approach really does not make that much sense to implement unless you really need other calculations than the array length. So best just to keep it in the document.

Answer 2

The best way to do so with MongoDB is to add new counter field to explicitly store the number of votes:

{ "product" : ObjectId("53ccfa53b502542b2f463acd"),
  "_id" : ObjectId("53e8675aea39355818ec4ab2"),
  "vote" : [ {...}, {...}, {...} ],
  "vote_count": 3, // <-- new field
  "review" : "Blah Blah Blah",
  "stars" : 3,
  "email" : "user@example.com", "__v" : 0 }

Of course, you have other options, like using Aggregation Pipeline. But adding new field is the best option, because it'll allow you to build an index on this field and do indexed queries.