CODEWITHSUNDEEP
phpsymfonydoctrine-orm
Clément Andraud
Clément Andraud

Reputation: 9269

Symfony2 doctrine complex query

I have two entites User and File.

I have a User OneToMany File relation. The field for relation from User is fileCommercial, and from File is commercial.

In the entity File i have a field state.

I need, in one query count all files with state=AAA, state=BBB etc... for a specific user (commercial).

For now i have : 

             $qb->select('a')
            ->from('ProjectCrmBundle:FiLe', 'a')
            ->leftjoin('a.commercial', 'c')
            ->where('c.type = :type')
            ->setParameter('type', 'Commercial');

With this query i can get all files for a user type=commercial. How can i get all counters like i said above ?

For example, if my User have 4 files with state AAA, and 3 with state BBB, i need to get theses number in my result... Thanks for help !

Upvotes: 1

Views: 60

Answers (1)

Erik
Erik

Reputation: 1057

Try something like this. It'll return an array that contains a field for state and one for the count. You'll probably have to inspect/var_dump it for the exact layout.

$qb->select('a.state, COUNT(c) as count')
->from('ProjectCrmBundle:FiLe', 'a')
            ->leftjoin('a.commercial', 'c')
            ->where('c.type = :type')
            ->groupBy('a.state')
            ->setParameter('type', 'Commercial');

Upvotes: 1

Related Questions