Sort results by array size

Question

I have an collection named article, I need to sort objects returned by the size of an array it contains. What is the best way to do this? One I thought of is to retrieve the whole list of objects and manually sort it in JS. But I return the articles 10 at a time, so everytime this API gets called it'll have to do the unnecessary work of sorting the array.

Article.find({})
     .limit(10)
     .skip(req.params.page*10)
     //Something like using $project to make a new variable called votecount that counts objects in a given array.
     .sort({votecount:-1})
     .exec(function(err,arts){
      articleObj.articles = arts;
        if (arts.length<10){
          articleObj.reachedEnd = true;
        }
        res.json(articleObj);
     });

I need to count the votes.up. This is a sample object:

{
"_id" : ObjectId("55f50cfddcf1ad6931fb8dd4"),
"timestamp" : "2015-09-13T00:58:57-5:00",
"url" : "http://www.nytimes.com/2015/09/13/sports/floyd-mayweather-finishes-bout-and-maybe-his-career-with-lopsided-win-over-andre-berto.html",
"abstract" : "Mayweather’s victory by unanimous decision gave him a record of 49-0, the same as the legendary heavyweight Rocky Marciano.",
"title" : "Mayweather Wins Easily in What He Calls Last Bout",
"section" : "Sports",
"comments" : [ ],
"votes" : {
    "up" : [
        ObjectId("55e5e16934d355d61c471e48")
    ],
    "down" : [ ]
},
"image" : {
    "caption" : "Floyd Mayweather Jr. after learning he defeated Andre Berto in a unanimous decision.",
    "url" : "http://static01.nyt.com/images/2015/09/14/sports/13fight/13fight-mediumThreeByTwo210.jpg"
},
"__v" : 0
}

Answer 1

You need the .aggregate() method instead, as all "sort" arguments must be a field present in the document, and this way you can "project" the $size of the array into the document for sorting:

Article.aggregate(
    [
        { "$project": {
            "timestamp": 1,
            "url": 1,
            "abstract": 1,
            "title": 1,
            "section": 1,
            "comments": 1,
            "votes": 1,
            "image": 1,
            "voteCount": { 
                "$subtract": [
                    { "$size": "$votes.up" },
                    { "$size": "$votes.down" }
                ]
            }
        }},
        { "$sort": { "voteCount": -1 } },
        { "$skip": req.params.page*10 },
        { "$limit": 10 },
    ],
    function(err,results) {
        // results here
    }
);

Of course that comes at a cost since you need to essentially calculate the size on every iteration. So it is a better practice to keep the "count" of votes within the document with each update operation, and also better with Bulk Operations to suit all cases:

var bulk = Atricle.collection.intializeOrderedBulkOp();

// Swap out a downvote where present
bulk.find({ 
    "_id": id, 
    "votes.up": { "$ne": userId },
    "votes.down": userId
}).updateOne({
    "$push": { "votes.up": userId },
    "$pull": { "votes.down": userId }
    "$inc": { "voteCount": 2 }
});

// Add an upvote where not present
bulk.find({ 
    "_id": id, 
    "votes.up": { "$ne": userId },
    "votes.down": { "$ne": userId }
}).updateOne({
    "$push": { "votes.up": userId },
    "$inc": { "voteCount": 1 }
});

bulk.execute(function(err,response) {
    // maybe do something here
});

Of course a "downvote" is the reverse of this process.

The point here is that as each vote is processed the "count" or "score" is kept updated at the same time. This allows a normal query and sort, without the need to calculate on each time the data is accessed since it is already done.

That latter case is the most performant way to handle this.