Darkstarone Darkstarone - 1 month ago 5
PHP Question

Symfomy Doctrine query - get a list of children for each parent.

What I'm currently trying to do is create a query that returns a result with parent names and a list of child names.

For example, say we have two tables

Category
and
Product
, where
Category
has many
Product
entities. I understand that I can directly access the children using
getProducts()
, or if I didn't have an association setup using shared IDs and joins. However, it's at this point I'm unsure how to proceed, in essence, I want a result that looks like this:

| Category.name | Product.name |
| Cars | BMW, Volvo, Fiat
| Bikes | BMX, Mountain Bike, Road Bike


I think the SQL term I'm looking for is GROUP_CONCAT, but that doesn't appear to be in doctrine2. So before I go marching off attempting to install an extension I don't really understand, I'd like to know if there is a simpler solution using Doctrine and the default query builder?

Answer

You can simply use fetch joins for your case.
Check the Doctrine documentation on fetch join here in chapter 14.2.2. Joins.

In a query builder you do fetch join like this:

$queryBuilder->select('c', 'p')
   ->from('Module\Entity\Category', 'c')
   ->leftJoin('c.products', 'p');
$results = $queryBuilder->getQuery()->getResult();

Now $results holds a collection of categories where each category has its product collection populated.


UPDATE

I would say it would be better to get the objects and create the array from the objects in the view (or whereever you need it).
If you really want to get only the fields you can change the select clause to select the fields you want instead. Not sure if the resulting array is exactly what you want but you can try it:

$queryBuilder->select('c.name', 'p.name')
    ->from('Module\Entity\Category', 'c')
    ->leftJoin('c.products', 'p');
$results = $queryBuilder->getResult();

Now results holds an array with the fields you selected.