Can I directly get a nested list in my Doctrine result?

Question

Sorry if the question is poorly phrased, I couldn't come up with a good way of describing my issue.

So basically, I'm using the Doctrine query builder to try and a list of training sessions from my Session entity. For each session, I need to fetch basic properties such as the name and date, but I also need to fetch the list of participants. There is a one-to-many relation between the entities Session and Participant, as there may be several participants to a given session. I would simply need a list of these participants, nested in each item of the list of sessions. Something like this:

[
    {
        "session_name": "name1",
        "session_date": "date1",

        ...

        "participants": [
            {
                "participant_name": "john1",

                ...
            },
            {
                "participant_name": "john2",

                ...
            },

            ...
        ],
    },
    {
        "session_name": "name2",
        "session_date": "date2",

        ...

        "participants": [
            {
                "participant_name": "john3",

                ...
            },
            {
                "participant_name": "john4",

                ...
            },

            ...
        ],
    },

    ...
]

This seems to me like it should be quite basic, but for the life of me I cannot get it to work with JOINs, subqueries, etc. The closest I got was this error, which does imply I'm trying to get a nested array (but it won't let me):

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

I had this error running this code:

$query = $this->createQueryBuilder('s')
    ->select('
        s.name,
        s.date,
        (SELECT p.nom FROM '.Participant::class.' p WHERE p.session = s.id) participants
    ')
    ->getQuery()->getArrayResult();

I know I could just fetch my sessions, then loop through them and fetch their participants with another DQL query, but obviously that doesn't seem like the proper way to do it. Is there a better way?

Answer 1

You can directly do a leftJoin on your QueryBuilder :

$queryResult = $this->createQueryBuilder('s')

    // After that next line, you can reference participants as 'p'
    ->leftJoin('s.participants', 'p')

    // If you intend to loop on the result, to avoid the N+1 problem, add this line :
    ->addSelect('p')

    ->getQuery()
    ->getResult()
;