Doctrine Query Join sub Query Syntax

Question

I've been trying to transfer the following raw sql into dql and I wasn't able to do for four hours, can you help me ? this query finds the duplicate hashes in database and gives me the ids of orders

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 ;

and my dql now:

    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

but the h1.orderis giving syntax error, as well everything else does:

  [Semantical Error] line 0, col 40 near '(SELECT h1.hash': Error: Class '(' is not defined.

I tried it as a subquery:

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

it gives me:

[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

As far as I know doctrine queries doesn't accept sub queries at all. Do the subquery before and enter the return to your main query — Pete

user181452 user181452 · Accepted Answer · 2016-01-26T16:23:48

my solution :

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

Doctrine Query Join sub Query Syntax

1 Answers