Symfony 3.4 site

There are 2 tables:

product enter image description here

and category

enter image description here

It is necessary to display information from these tables in the form: enter image description here

So that displayed the number of products in each category of goods

I wrote this DQL query:

SELECT a.id, a.category, a.enabled, COUNT(b.id) as prcnt FROM AppBundle:Category a JOIN AppBundle:Product b WITH a.id = b.category WHERE a.enabled = :enabled

And in the debugging output the result:

 array:1 [▼ 0 => array:4 [▼ "id" => 1 "category" => "Фантастика" "enabled" => true "prcnt" => "6" ] ]

As you can see, only 1 category was found, in addition, COUNT correctly counted, in the category “Fantastic” 4 products, and he considered all products in general - 6.

Where is the error in my DQL query? Why is the output only 1 category? How to write this DQL query correctly? And why in WITH inserted instead of ON in DQL ??

And in general, according to best practice in symfony is it better to write DQL queries or use the createQueryBuilder collector?

  • 2
    strange selection of aliases for tables. why not p and c ? and where is the grouping if the units are supposed to be used? - teran 4:26

1 answer 1

To display results for a specific category, you must perform grouping. In this case:

 SELECT a.id, a.category, a.enabled, COUNT(b.id) as prcnt FROM AppBundle:Category a JOIN AppBundle:Product b WITH a.id = b.category WHERE a.enabled = :enabled GROUP BY a.id