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