Laravel mongoDB group by combine with where in

Question

Here is my current code

    $results = Post::whereIn('category_id', $category_ids)
        ->raw(function($collection) {
            return $collection->aggregate([
                [   
                    '$group' => [
                        '_id' => '$user_id',
                        'count' => [
                            '$sum' => 1
                        ]   
                    ]   
                ],  
                [   
                    '$sort' => [
                        'count' => -1
                    ]   
                ],  
                [   
                    '$limit' => 10
                ],  
            ]); 
        });

I'm trying to get the posts that linked with certain categories, but this $results return all the posts.

How to modify this into where in + group by?

Edit (2016-07-14 10:13am)

Category

{
    "_id": ObjectID("578489618a8920afcb3f4de2"),
    "updated_at": ISODate("2016-07-13T06:21:40.700Z"),
    "created_at": ISODate("2016-07-12T07:16:49.913Z"),
}

Post

{
    "_id": ObjectID("578499629a89202fcb3f4de3"),
    "user_id": "578299629989e02fcb3f4de3",
    "title": "How to deal with MongoDB",
    "category_id": "578489618a8920afcb3f4de2",
    "updated_at": ISODate("2016-07-12T07:16:50.512Z"),
    "created_at": ISODate("2016-07-12T07:16:50.512Z")
}

{
    "_id": ObjectID("578499629a89202fcb3f4de3"),
    "user_id": "578299629989e02fcb3f4de3",
    "title": "It's so weird~",
    "category_id": "578489618a8920afcb3f4de2",
    "updated_at": ISODate("2016-07-12T07:16:50.512Z"),
    "created_at": ISODate("2016-07-12T07:16:50.512Z")
}

So 1 category has multiple posts, I want to list out each user has how many posts, and sort by descending order.

Now the grouping & sorting already works, just cannot filter particular category_id

Expected output

| User  | Total posts |
|-------|-------------|
| Smith | 11          |
| Ivy   | 8           |
| Paul  | 3           |

Answer 1

Okay, based on your (weird) collections and if I did understand it well, it should be something like this:

<?php 

$category_ids = []; //categories goes here

$results = Post::raw(function($collection) use ($category_ids) {
    return $collection->aggregate([
        //where in...
        [
            '$match' => [
                'category_id' => ['$in' => $category_ids]
            ]       
        ],  
        [   
            '$group' => [
                '_id' => '$user_id',
                'count' => ['$sum' => 1]   
            ]   
        ],  
        [   
            '$sort' => ['count' => -1]   
        ],  
        [   
            '$limit' => 10
        ],  
    ]); 
});

The first stage of pipeline uses $match and $in in order to filter posts where post.category_id is in $category_ids.
I should do the trick!