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?

score 3 · Accepted Answer · answered Sep 07 '17 at 11:04

3

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.

answered Sep 07 '17 at 11:04

Jakub Matczak

15,341
5
46
64

You are not wrong, but this is a Doctrine question. Doctrine is an ORM. I'm asking if there's a native way to do this out of the box with Doctrine. I'm generally creating these structures from the result set or using a custom hydrator to get what I want. I consider this a pretty common task, so I'm wondering if there's a way to do this natively in Doctrine. (I expect the answer to be no btw) – Thomas Maurstad Larsson Sep 07 '17 at 12:29
No, there's no way to do this natively with Doctrine. BTW, you have no object structure which would represent such grouping, so there's no way to use ORM here. You would need maybe some kind of a `Round` entity, but still I'm not sure if it would be possible. – Jakub Matczak Sep 07 '17 at 12:38
Sad to hear. I've accepted your answer based on that you where first. – Thomas Maurstad Larsson Sep 07 '17 at 13:22

score 1 · Answer 2 · answered Sep 07 '17 at 12:55

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.

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

2 Answers2