0

How can I use limit,count and sort with distinct in mongo from PHP ? I am using mongo command functionality and the code is like :

$data_count= $mongoInstance>command(array("distinct"=>"collection","key"=>"key","query"=>$filter_query));

How can I associate count, sort and limit with this ?

There is some aggeregate framework in mongo but how can I use it in PHP ?

Community
  • 1
  • 1
Happy Coder
  • 4,255
  • 13
  • 75
  • 152

1 Answers1

1

I belive you are actually looking for the aggregation framework. The distinct command is quite limited in what it can do. An example of what you're looking for would be:

$db->col->aggregate(array(
    array('$match'=>$filter_query)
    array('$group'=>array('_id'=>'$key', 'count'=>array('$sum'=>1))),
    array('$sort'=>array('_id'=>-1)),
    array('$skip'=>4),
    array('$limit'=>20)
));

Further reference can be found here: http://docs.mongodb.org/manual/reference/aggregation/operator-nav/

Sammaye
  • 43,242
  • 7
  • 104
  • 146
  • Thanks @Sammaye. It is working. One more little help .I just need to know the total count, for the pagination purpose. I mean this will only select the 20 values and I would like to know how many unique results are there. – Happy Coder Oct 04 '13 at 06:34
  • Also need to include some more fields to select. I think I can include it in the $project. I need to include email,timestamp and event fields to be selected. – Happy Coder Oct 04 '13 at 07:02
  • 1
    @AlwinAugustin I think like in SQL you might have to do two queries to get the total count, if you need more fields just include them into the $group operator like `'email'=>'$email'` – Sammaye Oct 04 '13 at 07:11
  • Thanks.I got the count. Now when I given array('$group'=>array('_id'=>'$email','email'=>'$email','timestamp'=>'$timestamp','event'=>'$event')) it is retunring an error which says [errmsg] => exception: the group aggregate field 'email' must be defined as an expression inside an object – Happy Coder Oct 04 '13 at 07:16
  • 1
    @AlwinAugustin Ooops sorry yeah it is grouping, try: `'email'=>array('$max'=>'$email')`, gotta tell MongoDB what value you want if you are dealing with duplicates – Sammaye Oct 04 '13 at 07:25
  • got it using array('$group'=>array('_id'=>array('email'=>'$email','timestamp'=>'$timestamp','event'=>'$event'))) . This is also working – Happy Coder Oct 04 '13 at 07:30
  • I'm using mongodb 1.0.7. It's not supported in this version. can you suggest any ideas? – user2003356 Dec 02 '13 at 08:54
  • @user2003356 you could use the `group` command though this is not recommended since it is basically a map reduce and isn't really supported with sharding. Ideally you would want to upgrade basically – Sammaye Dec 02 '13 at 08:57
  • @Sammaye: Thanks. I could understand. But, can I do it with group - map reduce? Can you advice me.. Because I am not clear with map/reduce. Your comments will be helpful for me. Thanks in advance. – user2003356 Dec 02 '13 at 09:06
  • @user2003356 Here is a well versed documentation page on the matter: http://docs.mongodb.org/manual/reference/command/group/ – Sammaye Dec 02 '13 at 09:14
  • @Sammaye: I need your help for aggregation with count concept. can you please check the question. http://stackoverflow.com/questions/20348093/mongodb-aggregation-how-to-get-total-records-count – user2003356 Dec 03 '13 at 10:09
  • @Sammaye: How can I get total records count using aggregation? Please advice me. – user2003356 Dec 03 '13 at 10:12