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?

Is there a reason why you want an array and not the object? – goulashsoup May 25 '21 at 07:46 — goulashsoup, May 25 '21 at 07:46

score 0 · Answer 1 · answered Oct 15 '20 at 17:28

0

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()
;

answered Oct 15 '20 at 17:28

Diabetic Nephropathy

190
1
10

I have tried `addSelect()`, and it just won't do anything if I already used `select()` beforehands. It feels to me like I'd have to work only with `addSelect()`, which won't let me choose which fields I want to fetch or not. Here I only need the `participant`'s first and last names, so I do not wish to fetch every single field which may be irrelevant in my case (e.g their phone number, address, etc) – ntyss Oct 16 '20 at 13:48

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

1 Answers1