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