How to get the next array from a database?

Question

Good to make this clearer: I use doctrine

I have a table Brands and Products

Brand
  id
  name

Product
  id
  name
  brand_id

I have many brands and products of these brands in the database. I would like to get a List of Brands (+ the number of its products) Grouped by Brand.name first last.

ex: 
array( 
   n => array( 
        0 => array('Nike', 4 ),
        1 => array('North Pole', 18) 
        .....
   )
   .....
)

So my question is: this can be done with a single query in an efficient way. I really don't want to run separate queries for each brand. First first. The doctrines of Hierarchical Data pass into my head, but I believe in it differently. thank

Answer 1

, Hydrator, .

, 3

• .name
• brand.name
• (product.id)

$results = $q->execute(array(), 'group_by_first_column');

Answer 2

, , . foreach.

Answer 3

Doctrine SQL- . SQL-:

SELECT 
  brand.name,
  count(product.id) 
FROM 
  brand 
JOIN 
  product ON 
  brand.id=product.brand_id 
GROUP BY 
  brand.id ORDER BY brand.name;

PHP , . Brand Name, . , , DQL, .