使用 Doctrine QueryBuilder 来自 Select 语句的 INNER JOIN 结果 [英] INNER JOIN Results from Select Statement using Doctrine QueryBuilder

查看：43 发布时间：2021/12/10 11:16:04 mysql symfony doctrine-orm query-builder

本文介绍了使用 Doctrine QueryBuilder 来自 Select 语句的 INNER JOIN 结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

您能否使用 Doctrine QueryBuilder 从包含 GROUP BY 的完整 SELECT 语句中INNER JOIN 来INNER JOIN 临时表?

Can you use Doctrine QueryBuilder to INNER JOIN a temporary table from a full SELECT statement that includes a GROUP BY?

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

The ultimate goal is to select the best version of a record. I have a viewVersion table that has multiple versions with the same viewId value but different timeMod. I want to find the version with the latest timeMod (and do a lot of other complex joins and filters on the query).

最初人们假设你可以先做一个GROUP BY viewId然后ORDER BY timeMod，但ORDER BY对GROUP BY没有影响，而且MySQL会返回随机结果.有很多答案(例如此处)解释了使用 GROUP 的问题并提供了解决方案，但我我在解释 Doctrine 文档时遇到了麻烦，无法找到一种使用 Doctrine QueryBuilder 实现 SQL 的方法(如果可能的话).为什么我不只使用 DQL?我可能不得不这样做，但我有很多动态过滤器和连接，使用 QueryBuilder 更容易实现，所以我想看看这是否可行.

Initially people assume you can do a GROUP BY viewId and then ORDER BY timeMod, but ORDER BY has no effect on GROUP BY, and MySQL will return random results. There are a ton of answers out there (e.g. here) that explain the problem with using GROUP and offer a solution, but I am having trouble interpreting the Doctrine docs to find a way to implement the SQL with Doctrine QueryBuilder (if it's even possible). Why don't I just use DQL? I may have to, but I have a lot of dynamic filters and joins that are much easier to do with QueryBuilder, so I wanted to see if that's possible.

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 语句，例如

Theoretical Solution via Query Builder (not working)

I am thinking that the JOIN needs to inject a DQL statement, e.g.

$em = $this->getDoctrine()->getManager();
$viewVersionRepo = $em->getRepository('GutensiteCmsBundle:ViewViewVersion');

$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 above example perfectly) outputs:

SELECT e, view, contentType 
FROM GutensiteCmsBundleEntityViewViewVersion e 
INNER JOIN (
    SELECT MAX(v.timeMod) AS timeModMax, v.viewId 
    FROM GutensiteCmsBundleEntityViewViewVersion 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 中尝试上述方法时，出现错误:

This Answer seems to indicate that it's possible in other contexts like IN statements, but when I try the above method in the JOIN, I get the error:

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