Subquery combined with dql doctrine

Question

I want to use DQL to create a query that looks like this in SQL:

select e.* from e inner join ( select uuid, max(locale) as locale from e where locale = 'nl_NL' or locale = 'nl' group by uuid ) as e_ on e.uuid = e_.uuid and e.locale = e_.locale 

I tried using QueryBuilder to generate a query and a subquery. I think they are doing everything right, but I cannot combine them in a connection statement. Anyone now if this is possible with DQL? I can’t use my own SQL because I want to return real objects, and I don’t know for what object this query is being executed (I only know the base class that has the uuid and locale property).

  $subQueryBuilder = $this->_em->createQueryBuilder(); $subQueryBuilder ->addSelect('e.uuid, max(e.locale) as locale') ->from($this->_entityName, 'e') ->where($subQueryBuilder->expr()->in('e.locale', $localeCriteria)) ->groupBy('e.uuid'); $queryBuilder = $this->_em->createQueryBuilder(); $queryBuilder ->addSelect('e') ->from($this->_entityName, 'e') ->join('('.$subQueryBuilder.') as', 'e_') ->where('e.uuid = e_.uuid') ->andWhere('e.locale = e_.locale'); 

Answer 1

You cannot put a subquery in the FROM your DQL.

I assume your PC {uuid, locale} , as well as the discussion with you in the IRC. Since you also have two different columns in your query, this can become ugly. What you can do is put it in a WHERE :

 select e from MyEntity e WHERE e.uuid IN ( select e2.uuid from MyEntity e2 where e2.locale IN (:selectedLocales) group by e2.uuid ) AND e.locale IN ( select max(e3.locale) as locale from MyEntity e3 where e3.locale IN (:selectedLocales) group by e3.uuid ) 

Note that I used a comparison with a (non-empty) array of locales to which you are attached to :selectedLocales . This is done in order to avoid destroying the query cache if you want to combine it with additional locales.

I also do not suggest creating this using the query builder if there is no real advantage in this, since it will simply simplify the splitting of the query cache if you add conditional expressions dynamically (this is also connected with 3 query builders!)