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

问题描述

您可以使用Doctrine QueryBuilder从$ {code> INNER JOIN 一个完整的 SELECT 语句中的临时表，其中包含一个 GROUP BY ？

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

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

<一个href =https://stackoverflow.com/a/11925750/3334390>这个答案似乎表明它可能在其他上下文如 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

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.

提示：该表应包含这两个值（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.

这篇关于INNER JOIN使用Doctrine QueryBuilder的Select语句的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！