convert query from MySql query To mongoDB query usin php driver

Question

I have to convert this query from mysql to mongoDB using php

SELECT `member_id`, `social_network_id`, `content_type_id` from recent_activity where  content_type_id  = 10  AND social_network_id = 9 group by member_id  order by  recent_activity_id  desc limit 5

I need the result like :

array(2) {
        ["content_id"]=>
        string(6) "122558"
        ["member_id"]=>
        string(6) "180306",
        ["content_type_id"]=>
        string(6) "10",
        ["social_network_id"]=>
        string(6) "9",
      },
      array(2) {
        ["content_id"]=>
        string(6) "122549"
        ["member_id"]=>
        string(6) "180305",
        ["content_type_id"]=>
        string(6) "10",
        ["social_network_id"]=>
        string(6) "9",
      },
      array(2) {
        ["content_id"]=>
        string(6) "122528"
        ["member_id"]=>
        string(6) "180304",
        ["content_type_id"]=>
        string(6) "10",
        ["social_network_id"]=>
        string(6) "9",
      },

I tried Aggregation Framework as

 $result = $collection->aggregate(array(
            '$match' => array('content_type_id'=>"10", "social_network_id"=>"9"),
            '$project' => array('content_type_id'=>1, "social_network_id"=>1, "content_id"=>1),
            '$group' => array('_id' => array('member_id'=>'$member_id')),
            '$sort'  => array('_id'=>-1),
            '$limit' => 5,
        ));

But i got this error

["errmsg"]=>
  string(80) "exception: A pipeline stage specification object must contain exactly one field."

and I tried

$result = $collection->aggregate(array(
                    '$match' => array('content_type_id'=>"10", "social_network_id"=>"9"),
                    '$project' => array("_id"=>'$content_id' ,'content_type_id'=>1),
                    '$group' => array('_id' => array('content_id'=>'$content_id', 'member_id'=>'$member_id')), 
                )
            );

And I tried this:

$array = array(
                array('$match' => array('content_type_id'=>"10", "social_network_id"=>"9")),
                array(
                    '$group' => array(
                        "_id" => array("member_id" => '$member_id'),
                    ),
                ),
                array('$limit'=>20),
                array('$sort'=>array('_id'=>-1)),
            );
        $results = $collection->aggregate($array);

But the result does not mach my expectation , the grouping working well but its return only member_id with other fields as

array(2) {
  ["result"]=>
  array(2) {
    [0]=>
    array(1) {
      ["_id"]=>
      array(1) {
        ["member_id"]=>
        string(5) "18036"
      }
    }
    [1]=>
    array(1) {
      ["_id"]=>
      array(1) {
        ["member_id"]=>
        string(6) "180306"
      }
    }
  }
  ["ok"]=>
  float(1)
}

and to return other fields

So I need to get latest 5 activty for member grouped by by member so the result should be 5 member with 5 activity with diferent member ids and list all data fro each member

My collection Data like :

{ "_id" : ObjectId("528e0b3d33df1b21228dc6a0"), "content_id" : "122542", "content_type_id" : "10", "date_added" : ISODate("2013-11-10T14:06:17Z"), "member_id" : "180306", "platform_id" : "2", "social_network_id" : "91" }

{ "_id" : ObjectId("5291a7a733df1bc3048b4567"), "content_id" : "122542", "content_type_id" : "10", "date_added" : ISODate("2013-11-10T14:06:17Z"), "member_id" : "180326", "platform_id" : "2", "social_network_id" : "9" }


{ "_id" : ObjectId("5291a7a733df1bc3048b4567"), "content_id" : "220252", "content_type_id" : "10", "date_added" : ISODate("2013-11-10T14:06:17Z"), "member_id" : "180316", "platform_id" : "2", "social_network_id" : "9" }

{ "_id" : ObjectId("5291a7a733df1bc3048b4567"), "content_id" : "110252", "content_type_id" : "10", "date_added" : ISODate("2013-11-10T14:06:17Z"), "member_id" : "180316", "platform_id" : "2", "social_network_id" : "9" }

I'm new to mongoDB AND I spent a lot of time to convert this query and to solve this error , any one can help me

Thanks

Answer 1

The correct query is:

$array = array(
            array('$match' => array('content_type_id'=>"10", "social_network_id"=>"9")),
            array('$project' => array( 'member_id'=>1, 'social_network_id'=>1, 'content_type_id'=>1)),
            array('$group' => array("_id" => array("member_id" => '$member_id', 'social_network_id'=>'$social_network_id', 'content_type_id'=>'$content_type_id'))),
            array('$limit'=>20),
            array('$sort'=>array('recent_activity_id'=>-1)),
        );
$results = $collection->aggregate($array);

Use:

• $match to filter the initial set to aggregate
• $project to reshape the resulting document: add new fields or include fields from the original document
• $group to group by, don't forget to include all the fields you want to select.

---

note about your schema design:

consider changing your schema to avoid the group by (which can be slow on production)

you can use a structure like this:

{ 
  member_id: 1, 
  shared_contents: [ { social_network_id: 'fb', content_type_id: 1234 }, { ... } ],
  recent_activity_id: 12345
}

as you can see you can have arrays of sub-documents in your documents, in this way you have the data ready to be fetched. (be sure this doesn't interfere with the other use-cases of your application)

---

Update: to return also all the ids of the aggregated items, add $addToSet to the $group and $project:

array('$project' => array( 'member_id'=>1, 'social_network_id'=>1, 'content_type_id'=>1, 'content_id'=>1)),

and:

array('$group' => array("_id" => array("member_id" => '$member_id', content_ids: { $addToSet: "$content_id" }, 'social_network_id'=>'$social_network_id', 'content_type_id'=>'$content_type_id'))),

Answer 2

You have records which contain member_id and recent_activity_id and you want to return the five most recent activities for each member (for a particular social network and content type).

This cannot currently be done entirely in MongoDB. You need this feature to be available and it has not yet been implemented. The closest you can get is you can return all recent activities for each member and then limit the array to first five members on the client side.

Here is how you would do it for all recent activities:

db.recent_activity.aggregate( [
    { $match : { content_type_id:10, social_network_id:9} },
    { $sort  : { recent_activity_id:-1} },
    { $group : { _id : { member_id:"$member_id", 
                         content_type_id:"$content_type_id", 
                         social_network_id:"$social_network_id" 
                 },
                 activity : { $push : "$recent_activity_id" }
               }
    }
] );

If the $slice operator is implemented in aggregation framework, that would be another way you could limit the activity array to latest five but that's also not available in the current version.

Answer 3

Solution to

exception: A pipeline stage specification object must contain exactly one field.

$result = $collection->aggregate(array(
    array('$match' => array('content_type_id'=>"10", "social_network_id"=>"9")),
    array('$project' => array("_id"=>'$content_id' ,'content_type_id'=>1)),
    array('$group' => array('_id' => array('content_id'=>'$content_id', 'member_id'=>'$member_id')))
));

Note the array wrapping each pipeline