首页 > 解决方案 > 带有子选择的 Doctrine 查询构建器内部联接

问题描述

我在 SQL 中有这个查询,它工作正常:

SELECT tl.*
FROM table1 tl
INNER JOIN table2 tl2
ON tl.id = tl2.other_id
INNER JOIN
(
   SELECT other_id, MAX(date) maxDATE
   FROM table2
   GROUP BY other_id
) 
tlv2 ON tl2.other_id = tlv2.other_id AND
tl2.date = tlv2.maxDATE WHERE tl.access=0
ORDER BY tlv2.maxDATE DESC

现在的问题是,我似乎无法弄清楚如何将其转换为 Doctrine 查询构建器。

我有这个:

$subquery = $this->getEntityManager()->getRepository(Table2::class)
    ->createQueryBuilder('tl2')
    ->select(array('other_id','MAX(date) maxDate'))
    ->groupBy('other_id')
    ->getDQL();
    $qb = $this->createQueryBuilder('tl');
    $qb->select('tl')
       ->innerJoin(Table2::class,'tl2','WITH','tl.id = tl2.other_id')
       ->innerJoin("(".$subquery.")",'tlv2','WITH','tl2.date = tlv2.maxDATE')
       ->where('tl.access = 0')
       ->orderBy('tlv2.maxDATE','DESC');

但它给了我错误:

Subquery is not supported here

在我放置$subquery变量的那一行。

我似乎无法弄清楚我在这里做错了什么。我究竟做错了什么?

标签: phpsymfonydoctrine

解决方案


如此处所述如果您使用的是 ORM,则映射将失败,您只能在不加水的情况下运行它,以防您仅使用 DBAL,它按预期工作

如果您要使用本机查询解决方案并且仍然需要映射结果,那么您可以使用\Doctrine\ORM\EntityManager::createNativeQuerywith\Doctrine\ORM\Query\ResultSetMapping

$sql = <<<SQL
    SELECT tl.*
    FROM table1 tl
    INNER JOIN table2 tl2
    ON tl.id = tl2.other_id
    INNER JOIN
    (
       SELECT other_id, MAX(date) maxDATE
       FROM table2
       GROUP BY other_id
    ) 
    tlv2 ON tl2.other_id = tlv2.other_id AND
    tl2.date = tlv2.maxDATE WHERE tl.access=0
    ORDER BY tlv2.maxDATE DESC
SQL;


$rsm = new Query\ResultSetMapping();
$rsm->addEntityResult(Entity::class, 'e');
$rsm->addFieldResult('e','id', 'id');
$rsm->addFieldResult('e','date', 'createAt');

$result = $this->entityManager->createNativeQuery($sql, $rsm)->getResult();

推荐阅读