Reputation: 3060
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
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
Reputation: 43884
Instead of grouping with Map Reduce you can group with:
distinct
: How to "Group By" with MongoDB$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.
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