CODEWITHSUNDEEP
phpmongodbmapreducedoctrinedoctrine-odm
Nathan
Nathan

Reputation: 3060

MongoDB ODM How do I group by a certain field with map/reduce?

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()

Upvotes: 1

Views: 8183

Answers (2)

joeni
joeni

Reputation: 151

In addition to Sammaye's answer. Doctrine ODM does support group queries:

http://doctrine-mongodb-odm.readthedocs.org/en/latest/reference/query-builder-api.html#group-queries

Upvotes: 0

Sammaye
Sammaye

Reputation: 43884

Instead of grouping with Map Reduce you can group with:

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.

Upvotes: 4

Related Questions