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

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

Answer 2

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'))),