Optimize DQL with double join

Question

I have such a structure: single profile can attend do several studies and for each study there is statute field which is foreign key to study_statute. In HTML in each row I need to show information for profile: profile.name, profile.study.field, profile.study.statute. I've done this using

$profileRepository->findAll()

but it generates to many queries and I want to optimize. At the moment there is query fetching profiles , for each profile ask for studies and for each study ask for statute name. So if there are 2 profiles and each profile attends to 2 studies I have

1 (list) +1 (study list for profile #1) +2 (study names for each of above studies) +1 (study list for profile #2) +2 (study names for each of above studies) queries.

How to optimize this?

In plain PHP I'd do: fetch big table for all profiles joined with studies and study names and parse it to multidimensional arrays, f.e. $profile[1]['studies'][1]['name'].

Answer 1

You can use dql fetch join:

$profiles = $em->createQuery('select p, stu, sta from YourBundleName:Profile p join p.studies stu join stu.statute sta')
    ->getResult();

The result would be the same as if you would use findAll except one thing. All related entities would have been fetched. If you access the "study" or "statute" Doctrine does not need to lazy load the association with another query.