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!

mroman · Accepted Answer · 2018-02-06 12:45:54Z

2

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 }}])

answered Feb 6, 2018 at 12:45

mroman

1,3829 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Blex Over a year ago

+1 for pre-aggregated count. @realnsleo: take a look at docs.mongodb.com/manual/reference/operator/update/inc how it works.

realnsleo Over a year ago

Thank you @mroman for your answer! It guided me into finding the solution. And I agree with Alex for the pre-aggregated count. It is something I am going to implement because my collection has 5M+ documents (sigh). I will update the question with my answer now. Thanks so much!

realnsleo · Accepted Answer · 2018-02-06 14:15:22Z

I found the solution thanks to @mmroman. It got me a few tries to get it working with PHP syntax. Here it is. I have simplified it and hopefully it will help someone looking for the same.

$pipeline = [ // Github considered wrapping the pipeline in an array like so
    [
        '$match' => [ // Use match to limit results (better performance)
            'comments' => [ '$exists' => true ] // Work only on posts with comments
        ]
    ],
    [
        '$project' => [
            '_id'          => 1, // 1 = returns field to result, 0 = does not 
            'id'           => 1,
            'from'         => 1,
            'created_time' => 1,
            'commentCount' => [ '$size' => '$comments' ] // commentCount can be anything and $comments is the field that has the array you want to count
        ] 
    ],
    [ '$sort' => [ 'commentCount' => - 1 ] ],
    [ '$limit' => 5 ] // Limit to the 5 top. You can change this per your satisfaction
];

// Then finally pipe the line to the aggegate
$cursor = $collection->aggregate(
    $pipeline
);

Hopefully this helps someone else!

Regards,

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related