INNER JOIN Results from Select Statement using Doctrine QueryBuilder

Question

Is it possible to use the Doctrine QueryBuilder in an INNER JOIN temporary table from a full SELECT that includes GROUP BY ?

The ultimate goal is to choose the best version of the recording. I have a viewVersion table that has several versions with the same viewId but with a different timeMod. I want to find a version with the latest timeMod (and do many other complex associations and filters on request).

People initially assume that you can do GROUP BY viewId and then ORDER BY timeMod , but ORDER BY does not affect GROUP BY, and MySQL will return random results. There is a ton of answers (like here ) that explain the problem using GROUP and offer a solution, but it's hard for me to interpret Doctrine docs to find a way to implement SQL with Doctrine QueryBuilder (if possible). Why am I just not using DQL? I may have to, but I have a lot of dynamic filters and associations that are much easier to do with QueryBuilder, so I wanted to see if this is possible.

MySQL sample to play in Doctrine QueryBuilder

 SELECT vv.* FROM view_version vv #inner join only returns where the result sets overlap, ie one record INNER JOIN ( SELECT MAX(timeMod) maxTimeMod, viewId FROM view_version GROUP BY viewId ) version ON version.viewId = vv.viewId AND vv.timeMod = version.maxTimeMod #join other tables for filter, etc INNER JOIN view v ON v.id = vv.viewId INNER JOIN content_type c ON c.id = v.contentTypeId WHERE vv.siteId=1 AND v.contentTypeId IN (2) ORDER BY vv.title ASC; 

Theoretical solution using Query Builder (not working)

I think JOIN should introduce a DQL statement, for example.

 $em = $this->getDoctrine()->getManager(); $viewVersionRepo = $em->getRepository('GutensiteCmsBundle:View\ViewVersion'); $queryMax = $viewVersionRepo->createQueryBuilder() ->addSelect('MAX(timeMod) AS timeModMax') ->addSelect('viewId') ->groupBy('viewId'); $queryBuilder = $viewVersionRepo->createQueryBuilder('vv') // I tried putting the query in a parenthesis, to no avail ->join('('.$queryMax->getDQL().')', 'version', 'WITH', 'vv.viewId = version.viewId AND vv.timeMod = version.timeModMax') // Join other Entities ->join('e.view', 'view') ->addSelect('view') ->join('view.contentType', 'contentType') ->addSelect('contentType') // Perform random filters ->andWhere('vv.siteId = :siteId')->setParameter('siteId', 1) ->andWhere('view.contentTypeId IN(:contentTypeId)')->setParameter('contentTypeId', $contentTypeIds) ->addOrderBy('e.title', 'ASC'); $query = $queryBuilder->getQuery(); $results = $query->getResult(); 

My code (which may not match the example above):

 SELECT e, view, contentType FROM Gutensite\CmsBundle\Entity\View\ViewVersion e INNER JOIN ( SELECT MAX(v.timeMod) AS timeModMax, v.viewId FROM Gutensite\CmsBundle\Entity\View\ViewVersion v GROUP BY v.viewId ) version WITH vv.viewId = version.viewId AND vv.timeMod = version.timeModMax INNER JOIN e.view view INNER JOIN view.contentType contentType WHERE e.siteId = :siteId AND view.contentTypeId IN (:contentTypeId) ORDER BY e.title ASC 

This answer seems to indicate that it is possible in other contexts such as IN , but when I try to use the above method in JOIN, I get an error:

 [Semantical Error] line 0, col 90 near '(SELECT MAX(v.timeMod)': Error: Class '(' is not defined. 

Answer 1

Many thanks to @AdrienCarniero for his alternative query structure for sorting the highest version with a simple JOIN, where the timeMod entity is smaller than the combined timeMod table.

Alternative request

 SELECT view_version.* FROM view_version #inner join to get the best version LEFT JOIN view_version AS best_version ON best_version.viewId = view_version.viewId AND best_version.timeMod > view_version.timeMod #join other tables for filter, etc INNER JOIN view ON view.id = view_version.viewId INNER JOIN content_type ON content_type.id = view.contentTypeId WHERE view_version.siteId=1 # LIMIT Best Version AND best_version.timeMod IS NULL AND view.contentTypeId IN (2) ORDER BY view_version.title ASC; 

Using Doctrine QueryBuilder

 $em = $this->getDoctrine()->getManager(); $viewVersionRepo = $em->getRepository('GutensiteCmsBundle:View\ViewVersion'); $queryBuilder = $viewVersionRepo->createQueryBuilder('vv') // Join Best Version ->leftJoin('GutensiteCmsBundle:View\ViewVersion', 'bestVersion', 'WITH', 'bestVersion.viewId = e.viewId AND bestVersion.timeMod > e.timeMod') // Join other Entities ->join('e.view', 'view') ->addSelect('view') ->join('view.contentType', 'contentType') ->addSelect('contentType') // Perform random filters ->andWhere('vv.siteId = :siteId')->setParameter('siteId', 1) // LIMIT Joined Best Version ->andWhere('bestVersion.timeMod IS NULL') ->andWhere('view.contentTypeId IN(:contentTypeId)')->setParameter('contentTypeId', $contentTypeIds) ->addOrderBy('e.title', 'ASC'); $query = $queryBuilder->getQuery(); $results = $query->getResult(); 

In terms of performance, it really depends on the data set. See discussion for details .

TIP: the table should include indexes for both of these values ​​(viewId and timeMod) to speed up the results. I do not know if he will also use the same index in both fields.

Native SQL queries using the original JOIN method may be better in some cases, but compiling a query over an extended range of code that dynamically creates it and getting the correct mappings is a pain. So this is at least an alternative solution that I hope will help others.