How to get the following array from database?

Ok to make it more clear: I am Using doctrine

I have a table Brands and Products

Brand
  id
  name

Product
  id
  name
  brand_id

I have a lot of brands and Products of those brands in the database. I would like to retrieve List of brands(+ count of it开发者_如何学Pythons products) Grouped by Brand.name's first latter.

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

So my question was can this be done with one query in a efficient way. I really don't wan't to run separate queries for each brand.name's first latter. Doctrines "Hierarchical Data" cross my mind but I believe it for different thing?. thanks

If you are going to use this form of result more than once, it might be worthwhile to make the formatting into a Hydrator, as described here.

In your case, you can create a query that select 3 columns

1. first letter of brand.name
2. brand.name
3. count(product.id)

Then hydrate the result

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

You cannot take it from database in that way, but you can fetch data as objects or arrays and then transform it to described form. Use foreach loops.

When using Doctrine you can also use raw SQL querys and hydrate arrays instead of objects. So my Solution would be to use a native SQL Query:

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

And then iterate in PHP over the result to build the desired array. Because the Result is ordered by Brand Name this is quite easy. If you wasn't to keep database abstraction I think it should also be possible to express this query in DQL, just hydrate an array instead of objects.