Matheus Oliveira Matheus Oliveira - 16 days ago 9
PHP Question

How to "group by" field "count" in mongodb querybuilder?

I want to classify cities with the products they have.

I have two documents: Product and city. The product has its ID and a reference to a city document:

Product(id) City

P1 ------------ Atlanta

P2 ------------ New York

P3 ------------ Michigan

P4 ------------ Atlanta

....


I want as result of the query

[Atlant => 23, New York => 35, Michigan => 23, etc..]


But I not being able to get the result.

My actual code is

public function countBestUsers()
{
return $this->createQueryBuilder()
->select('id', 'city')
->distinct('city')
->getQuery()
->execute()
;
}

Answer

You can try with group and reduce, this example works for me:

$count = $dm->createQueryBuilder()->hydrate(false)
        ->group(array('city.$id' => 1), array('value' => 0))
        ->reduce('function (curr,result) {
            result.value++;
        }')
        ->getQuery()
        ->execute()->toArray();

If you need more examples : http://docs.doctrine-project.org/projects/doctrine-mongodb-odm/en/latest/reference/query-builder-api.html#group-queries