Possible to use groupBy column as associative array index for grouped collections with Doctrine query builder?

Question

Given a table structure like the following:

Matches:
| id | round | home_team | away_team |
|  1 |   1   | Juventus  |  Milan    |
|  2 |   1   | Inter     |  Roma     |
|  3 |   2   | Juventus  |  Inter    |
|  4 |   2   | Roma      |  Milan    |

... is it possible to build collections based on one of the columns? I would like all matches to be organised in collections based on the round column.

My current query builder looks like this:

/** @var MatchRepository $matchRepository */
$matchRepository = $em->getRepository('JCNApiBundle:Football\Match');

return $matchRepository->createQueryBuilder('m', 'm.round')
    ->where('m.competition = :competition')
    ->setParameter('competition', $competitionId)
    ->groupBy('m.id, m.round')
    ->getQuery()
    ->getArrayResult()
;

It unfortunately only returns one row per group: (One match per round)

[
    // Round 1
    1 => [
        // Match 1
        "id" => 1,
        "round" => 1,
        "home_team" => "Juventus",
        "away_team" => "Milan",
    ],
    // Round 2
    2 => [
        // Match 3
        "id" => 3,
        "round" => 2,
        "home_team" => "Juventus",
        "away_team" => "Inter",
    ]
]

I'm looking for something like this:

[
    // Round 1
    1 => [
        // Match 1
        0 => [
            "id" => 1
            "round" => 1
            "home_team" => "Juventus"
            "away_team" => "Milan"
        ],
        // Match 2
        1 => [
            "id" => 2
            "round" => 1
            "home_team" => "Inter"
            "away_team" => "Roma"
        ]
    ]
    // Round 2
    2 => [
        // Match 3
        0 => [
            "id" => 3
            "round" => 2
            "home_team" => "Juventus"
            "away_team" => "Inter"
        ],
        // Match 4
        1 => [
            "id" => 4
            "round" => 2
            "home_team" => "Roma"
            "away_team" => "Milan"
        ]
    ]
]

Is this possible with the Doctrine query builder?

Answer 1

No, this is not possible with SQL at all. SQL queries always return two-dimensional array. You want to get a three-dimensional one.

You need to skip GROUP BY part and iterate the returned collection to create desired structure in PHP.

Answer 2

As Jakub Matczak mentioned, it's not possible with SQL.

However if you want that your query returns such multidimensional array - you can write a custom hydrator. And inside that hydrator do this grouping by "round" manually. So, Doctrine allows you to separate this hydration/grouping logic in a separate class, but you still need to code it.