php - Doctrine Query Join 子查询语法

Question

我一直在尝试将以下原始 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]
[Semantical Error] line 0, col 40 near '(SELECT h1 FROM': Error: Class '(' is not defined.

[Doctrine\ORM\Query\QueryException]
SELECT h FROM PaymentBundle:Hash h JOIN (SELECT h1 FROM PaymentBundle:Hash h1 GROUP BY h1.hash HAVING COUNT(h1) > 1) dt ON h.hash = dt.hash

Answer 1

我的解决方案:

$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();