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

Question

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

@aynber, Sorry, I changed the instance names to ask the question here -- fixed . I also tried the group for the mongodb, didn't work. :x — Matheus Oliveira
– Matheus Oliveira, Commented Nov 22, 2016 at 16:08

panche14 · Accepted Answer · 2016-11-23 08:33:59Z

3

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

If you will sort it by the quantities, I recommend use aggregate Framework:

$pipeline = array('$group' => array(
        '_id' => '$city',
        'value' => array('$sum' => 1)
    )));

array_push($pipeline, array( '$sort' => array('value' => -1) ) );

$count = $dm->getDocumentCollection('YourBundle:Product')
            ->aggregate($pipeline)->toArray();

edited Nov 23, 2016 at 8:33

answered Nov 22, 2016 at 16:15

panche14

6614 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matheus Oliveira Over a year ago

It works pretty well. Thank you already! Would you also have a little code to sort it by the quantities?

panche14 Over a year ago

In this case I recommend you use aggregate Framework docs.mongodb.com/v3.2/aggregation I'll edit my answer to put an example with aggregate.

Matheus Oliveira Over a year ago

Hi @panche14, I used usort($array, function($a, $b) { return $b['value'] - $a['value']; }); To sort my array, Thanks for the other tip ;]

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related