使用Doctrine QueryBuilder从Select语句获得的内部联接结果

Question

您是否可以将Doctrine QueryBuilder用于包含一个INNER JOIN完整SELECT语句的临时表中GROUP BY？

最终目标是选择记录的最佳版本。我有一个viewVersion表，它具有具有相同viewId值但不同timeMod的多个版本。我想找到具有最新timeMod的版本（并对查询执行许多其他复杂的联接和过滤器）。

最初，人们认为您可以先执行a GROUP BY viewId，然后执行ORDER BY timeMod，但是ORDER BY对GROUP
BY无效，MySQL将返回随机结果。有大量的答案（例如在这里）来解释使用GROUP的问题并提供解决方案，但是我很难解释Doctrine文档以找到使用Doctrine
QueryBuilder实现SQL的方法（如果可能的话）
。我为什么不只使用DQL？我可能必须这样做，但是我有很多动态过滤器和联接，使用QueryBuilder可以轻松得多，所以我想看看是否有可能。

在MySQL QueryBuilder中复制的示例MySQL

SELECT vv.* 
FROM view_version vv
#inner join only returns where the result sets overlap, i.e. 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;

通过查询生成器的理论解决方案（不起作用）

我在想JOIN需要插入DQL语句，例如

$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();

我的代码（可能与上面的示例不完全匹配）输出：

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

该答案似乎表明在IN语句等其他上下文中也是可能的，但是当我在JOIN中尝试上述方法时，出现错误：

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

Answer 1

非常感谢@AdrienCarniero提供的替代查询结构，该查询结构使用简单的JOIN对最高版本进行排序，其中实体的timeMod小于联接表timeMod。

替代查询

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;

使用教义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();

在性能方面，它实际上取决于数据集。有关详细信息，请参见此讨论。

提示：该表应在这两个值（viewId和timeMod）上都包含索引，以加快结果的速度。 我不知道这是否也会从两个领域的单一索引中受益。

在某些情况下，使用原始JOIN方法的本机SQL查询 可能
会更好，但使用动态创建它的扩展代码范围编译该查询，并使映射正确将是一件痛苦的事情。因此，这至少是一种替代解决方案，希望对其他人有所帮助。