使用 Doctrine QueryBuilder 来自 Select 语句的 INNER JOIN 结果

2021-12-10 00:00:00 mysql symfony doctrine-orm query-builder

您能否使用 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.

推荐答案

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

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.

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;

使用 Doctrine QueryBuilder

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

$queryBuilder = $viewVersionRepo->createQueryBuilder('vv')
    // Join Best Version
    ->leftJoin('GutensiteCmsBundle:ViewViewVersion', '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.

提示:该表应包含这两个值(viewId 和 timeMod)的索引以加快结果速度.我不知道它是否也会从两个字段的单个索引中受益.

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

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.

相关文章