How to correctly retrieve records from a many to many relationship (Doctrine - DQL query)

Question

I have the following DQL query:

$qb->select('v, b, c, t, p, m, s, f, h')
    ->from('UrlBuilderBundle:Version', 'v')
    ->leftJoin('v.ddlBrands', 'b', 'WITH', 'b.version = v.id AND b.isActive = 1 AND v.isActive = 1')
    ->leftJoin('v.ddlCampaignObjectives', 'c', 'WITH', 'c.version = v.id AND c.isActive = 1')
    ->leftJoin('v.ddlThemes', 't', 'WITH', 't.version = v.id AND t.isActive = 1')
    ->leftJoin('t.ddlProducts', 'p', 'WITH', 'p.isActive = 1')
    ->leftJoin('v.ddlMediums', 'm', 'WITH', 'm.version = v.id AND m.isActive = 1')
    ->leftJoin('m.ddlSources', 's', 'WITH', 's.ddlMedium = m.id AND s.isActive = 1')
    ->leftJoin('v.fields', 'f', 'WITH', 'f.version = v.id AND f.isActive = 1')
    ->leftJoin('f.helpText', 'h', 'WITH', 'h.field = f.id AND h.isActive = 1');

$query = $qb->getQuery();
$versions = $query->getArrayResult();

All of the specified joins are between tables that have a many to one/one to many relationship except between the ddlProducts and ddlThemes tables. There is a many to many relationship (ddlProducts and ddlThemes) which is causing problems, it results in the following error:

Error: Maximum execution time of 30 seconds exceeded at /vendor/doctrine/orm/lib/Doctrine/ORM/Internal/Hydration/AbstractHydrator.php:296)"}

Can anyone point me in the right direction in resolving this error?

Answer 1

Well, querying and populating collections of 9 different interconnected entities will do that. But without more knowledge of your entire model, we cannot say what can be changed in the query.

Perhaps you can try finding tables that can be denormalized into some other one to avoid the extra join. Or perhaps you could split the query in more if you do not need everything in one uber table.

It really depends on what you need the resultset for