INNER JOIN Results from Select Statement using Doctrine QueryBuilder

Solution 1:

A big thanks to @AdrienCarniero for his alternative query structure for sorting the highest version with a simple JOIN where the entity's timeMod is less than the joined table timeMod.

Alternative Query

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 dataset. See this discussion for details.

TIP: The table should include indexes on both these values (viewId and timeMod) to speed up results. I don't know if it would also benefit from a single index on both fields.

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