CODEWITHSUNDEEP
mysqldoctrine-ormsymfony
vellmur
vellmur

Reputation: 272

Symfony Doctrine Group By

I need to get result from my database as associative array. I have gardens and families that related to this gardens as (One to Many). So for each garden i need to get array of families. So garden must be key, and array of families - it is a values. So i tried:

public function getFamiliesInGardens($client)
{
    $qb = $this->createQueryBuilder('g');

    $qb
        ->select(['g.name as garden', 'family.name', 'family.id'])
        ->join('g.growing', 'growing')
        ->join('growing.plant', 'plant')
        ->join('plant.family', 'family')
        ->where('g.client = :client')
        ->orderBy('g.name')
        ->addOrderBy('growing.endDate', 'DESC')
        ->setParameter('client', $client);

    $grow = $qb->getQuery()->getArrayResult();

    return $grow;
}

I got: 

[0] => array(3) {
    ["garden"]=> string(1) "1"
    ["name"]=>string(9) "Brassicas"
    ["id"]=> int(13)
}
[1] =>
    array(3) {
    ["garden"]=> string(1) "1"
    ["name"]=> string(13) "Miscellaneous"
    ["id"]=> int(18)
    }

But i expect: 

[0] => array(1) {
    ["1"] => array(2) {
        [0] => array(2) {
            ["name"] =>string(9) "Brassicas"
            ["id"] => int(13)
        },
        [1] => array(2) {
            ["name"]=>string(9) "Miscellaneous"
            ["id"]=> int(18)
        },
    }
}

If i will add group by garden, result will be the same as first, but garden will be chosed once without repeat. Same for family. So, what can i change to get array: garden => families?

Upvotes: 0

Views: 273

Answers (1)

Jovan Perovic
Jovan Perovic

Reputation: 20201

I came across this use case 3 years ago where I hoped the same. However, GROUP BY does not work that way.

You would have to manually iterate over the records and group them as you require. For example, something like this:

$gardens = ....
$groups = [];
for ( $gardens as $g ){
    if (!array_key_exists($g['id'], $groups)){
        $groups[$g['id']] = [];
    }

    $groups[$g['id']][] = $g;
}

Upvotes: 3

Related Questions