MongoDB ODM如何使用map / reduce对某个字段进行分组？

Question

I have a collection of documents that I want to query by a certain field called location, sort them by a pos field, and only return the highest value for each pos field.

So the query right now looks something like this:

$locations = array(1,2,3,4,5,6);

   $results = $this->dm->createQueryBuilder('\App\Document\Test')
        ->select('id', 'word', 'pos','change', 'title', 'coll_at', 'location')
        ->field('location')->in($locations)
        ->sort('location', 'asc')
        ->sort('pos', 'asc')
        ->getQuery()->execute();

But because there can be multiple entries for a specific location each with different pos, I then have to create a foreach loop to manipulate the data afterwards. In this scenario, I could take that shortcut just altering the data after it's returned, but I have other scenarios where it isn't efficient at all to do that. So I created this smaller scenario to try and figure out how to either use Doctrine ODM's group query, or even map & reduce it. Not sure the best way to. I see lots of examples of getting a running total, etc.

So how would I create a query to get the highest numerical value in the pos field for each specific location? Knowing that there can be multiple documents with the same location but a different pos value. And on top of that, have all the fields for the selected record that I have listed above in the ->select()

Answer 1

Instead of grouping with Map Reduce you can group with:

• group() or in Doctrines case distinct: How to "Group By" with MongoDB
• the aggregation framework using $group: http://docs.mongodb.org/manual/reference/aggregation/group/

Do note that I believe the default implementation of Doctrines distinct actually works on the old group() command for MongoDB which is basically an MR wrapped however:

So how would I create a query to get the highest numerical value in the pos field for each specific location

That might work, however if not then you will need to use the aggregation framework, however in Doctrine this is a little harder since they don't seem to have a true helper that links into the rest of their framework for it: https://github.com/doctrine/DoctrineMongoDBBundle/issues/165 so you must the command function to run it.

Edit

I am a bit of a Doctrine noob myself so this is taken from examples:

$connection = $this->get('doctrine_mongodb')->getConnection();
$mongo = $connection->getMongo();
if(!$mongo){
    $connection->connect();
    $mongo = $connection->getMongo();
}
$db = $mongo->selectDB('test_database');
$aggregate_results = $db ->command(array( 
            "aggregate" => "my_collection",
            "pipeline" => 
                array( 
                    array( '$group' => array( 'location' => '$location', 'pos' => array('$sum' => '$pos'))),
                    array( '$sort' => array( "pos" =>1 ) )
                )
        ));

This will run the aggregation for you, it won't be exactly what your looking for I don't think but play around with it.