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

Your SQL statement is not legal SQL syntax - you cannot select multiple columns but only include some of them in your group by plus order by goes after the group by. Maybe you can explain in plain English what you are trying to do? Your listed output isn't clear - content_id is different but the other fields are the same - why is member_id the same - do you want to group by it as well? — Asya Kamsky
– Asya Kamsky, Commented Nov 24, 2013 at 0:55
@AsyaKamsky, I edit my question , I need to get latest 5 activty for member grouped by by member so the result should be 5 member with 5 activity with different member ids and list all data fro each member — Osama Jetawe
– Osama Jetawe, Commented Nov 24, 2013 at 7:58
@AsyaKamsky It's perfectly legal with Fisher Price My First SQL Server. Or, MySQL, as its sometimes known. Without any aggregate function it's basically SELECT DISTINCT ... ORDER BY ... LIMIT 5 — ta.speot.is
– ta.speot.is, Commented Nov 24, 2013 at 8:00
I'm no MongoDB expert (I do know that it's web-scale, however), but you don't need to aggregate anything in MySQL ... so you probably don't need to aggregate anything in MongoDB. docs.mongodb.org/manual/reference/command/distinct — ta.speot.is
– ta.speot.is, Commented Nov 24, 2013 at 10:44

Ryan Schumacher · Accepted Answer · 2013-11-26 22:44:00Z

2

+25

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

answered Nov 26, 2013 at 22:44

Ryan Schumacher

1,9162 gold badges22 silver badges33 bronze badges

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

1 Comment

Osama Jetawe Over a year ago

Thanks for your answer, but its not working for me , it was return an empty set like array(2) { ["result"]=> array(1) { [0]=> array(1) { ["_id"]=> array(0) { } } } ["ok"]=> float(1) }

Madarco · Accepted Answer · 2013-12-01 07:24:14Z

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

edited Dec 1, 2013 at 7:24

answered Nov 28, 2013 at 18:35

Madarco

2,12418 silver badges26 bronze badges

3 Comments

Osama Jetawe Over a year ago

Thank you for your answer , but I need to return "content_id", if I include the ""content_id" inside the $group and $project, then the group by does not working, I include it like array('$project' => array( 'member_id'=>1, 'social_network_id'=>1, 'content_type_id'=>1, 'content_id'=>1)), array('$group' => array("_id" => array("member_id" => '$member_id', 'social_network_id'=>'$social_network_id', 'content_type_id'=>'$content_type_id','content_id'=>'$content_id'))),

Madarco Over a year ago

Sorry, but there is a logical flaw in your request: you are trying do aggregate MULTIPLE documents, and so you will have multiple content_id, not only one. This is why if you specify the content_id in the group the aggregation doesn't work. (try it with your original query on Mysql, there will be the same problem)

Madarco Over a year ago

I've added a solution, however this is no longer the original query

Asya Kamsky · Accepted Answer · 2013-11-30 17:01:21Z

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.

Collectives™ on Stack Overflow

convert query from MySql query To mongoDB query usin php driver

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related