Sorting a Mongo DB collection by size of Array Field in PHP

Question

New to MongoDB here and I am having a hard time achieving this. I have a database with a collection called posts. It has the following structure (in it's simplest form):

{
    "_id": ObjectId
    "title" : String
    "content" : String
    "comments" : Array
}

Using PHP with the new MongoDB driver, I wish to run a query that returns documents arranged by the number of comments. I used the following code but I am not sure if it's the right way to do it:

$cursor = $collection->find([],
    [
        'sort' => [ 'comments' => - 1 ]
    ]
);

Any help will be much appreciated! Thank you SO community!

Answer 1

You should be able to use the aggregation framework using a projection stage that computes the number of comments using the $size operator and then add a sort stage. However, this is likely going to be terribly slow because the count has to be computed each time you query it... so... if you want this often you might want to pre-compute the number of comments and create an index based on the pre-computed number. Something along the lines of:

db.col.aggregate([{$project: ... "numberOfComments" : 
   {$size : "$comments"},
 {$sort : { numberOfComments : -1 }}])