我一直在尝试将以下原始sql转移到dql中,但我无法做到四个小时,你能帮我吗?此查询查找数据库中的重复哈希并给我订单的 ID
SELECT h.order_id
FROM `Hash` h
INNER JOIN (SELECT `order_id`,hash
FROM `Hash`
GROUP BY `hash`
HAVING COUNT(*) > 1
) dt ON h.hash=dt.hash ;
和我的 DQL 现在:
SELECT h FROM PaymentBundle:Hash h
JOIN
(SELECT h1.order, h1.hash FROM PaymentBundle:Hash h1 GROUP BY h1.hash HAVING COUNT(h1) > 1)
dt WITH h.hash = dt.hash
但是h1.order
给出了语法错误,以及其他所有错误:
[Semantical Error] line 0, col 40 near '(SELECT h1.hash': Error: Class '(' is not defined.
我尝试将其作为子查询:
$subDql = 'SELECT h1 FROM PaymentBundle:Hash h1 GROUP BY h1.hash HAVING COUNT(h1) > 1';
$subQuery = $this->getEntityManager()
->createQuery($subDql);
$dql = 'SELECT h FROM PaymentBundle:Hash h JOIN ('.$subQuery->getDQL().') dt ON h.hash = dt.hash';
$query = $this->getEntityManager()
->createQuery($dql);
return $query->getResult();
它给了我:
[Doctrine''ORM''Query''QueryException]
[语义错误] 第 0 行,第 40 行靠近"(从中选择 h1":错误:类 未定义"("。[Doctrine''ORM''Query''QueryException]
从付款捆绑中选择 h:哈希 h 连接(从中选择 h1 付款捆绑包:哈希 h1 分组由 h1.哈希 具有计数(h1)> 1) dt 开 h.hash = dt.hash
我的解决方案:
$rsm = new ResultSetMappingBuilder($this->getEntityManager());
$rsm->addRootEntityFromClassMetadata('PaymentBundle:Hash', 'h');
$qb = $this->getEntityManager()->createNativeQuery("
SELECT h.id, h.order_id
FROM `Hash` as h
INNER JOIN (SELECT `order_id`,hash
FROM `Hash`
GROUP BY `hash`
HAVING COUNT(*) > 1
) dt ON h.hash = dt.hash
", $rsm);
return $qb->getResult();