SELECT [...] FROM (SELECT [...]) in DQL

Question

I have the following query :

SELECT COUNT(a0_.id) AS sclr0, LOWER(a0_.city) AS sclr1 
FROM address a0_ 
INNER JOIN customer c1_ ON (c1_.customer_address = a0_.id) 
WHERE a0_.city IS NOT NULL 
AND a0_.city <> '' 
AND (
    c1_.id IN (
        SELECT p2_.customer AS sclr2 
        FROM payment p2_ 
        WHERE p2_.init <> 1 
        AND p2_.unpaid <> 1 
        AND p2_.cancel_date IS NULL 
        AND (p2_.value > 0 OR p2_.date BETWEEN '2011-03-23 00:00:00' AND '2011-03-23 23:59:59') 
        AND p2_.customer IS NOT NULL 
        GROUP BY p2_.customer
    )
) GROUP BY sclr1
LIMIT 5

I built with the following DQL :

$qb = $this->createQueryBuilder('a')
->select('COUNT(a) AS total, LOWER(a.city) AS city')
->join('EvoUserBundle:Customer', 'c', 'WITH', 'c.customerAddress = a')
->where('a.city IS NOT NULL')
->andWhere('a.city != \'\'')
->groupBy('city')
->setMaxResults(5);

$subquery = $this->em->getRepository('EvoSaleBundle:Payment')->createQueryBuilder('p')
->select('IDENTITY(p.customer)')
->andWhere('p.init != 1')
->andWhere('p.unpaid != 1')
->andWhere('p.cancelDate IS NULL')
->andWhere('p.value > 0 OR p.date BETWEEN \'2011-03-23 00:00:00\' AND \'2011-03-23 23:59:59\'')
->andWhere('p.customer IS NOT NULL')
->groupBy('p.customer');

$qb->andWhere($qb->expr()->in('c', $subquery->getDQL()));

and returns the following data set :

array(10) {
  [0]=>
  array(2) {
    ["total"]=>
    string(1) "3"
    ["city"]=>
    string(18) "epinay sur seine"
  }
  [1]=>
  array(2) {
    ["total"]=>
    string(1) "7"
    ["city"]=>
    string(8) "firfol"
  }
  [2]=>
  array(2) {
    ["total"]=>
    string(1) "2"
    ["city"]=>
    string(20) "fontenay sous bois"
  }
  [3]=>
  array(2) {
    ["total"]=>
    string(1) "1"
    ["city"]=>
    string(13) "puyravault"
  }
  [4]=>
  array(2) {
    ["total"]=>
    string(1) "3"
    ["city"]=>
    string(8) "torcy"
  }
}

The problem appears when I add an ORDER BY clause to the query :

SELECT COUNT(a0_.id) AS sclr0, LOWER(a0_.city) AS sclr1 
FROM address a0_ 
INNER JOIN customer c1_ ON (c1_.customer_address = a0_.id) 
WHERE a0_.city IS NOT NULL 
AND a0_.city <> '' 
AND (
    c1_.id IN (
        SELECT p2_.customer AS sclr2 
        FROM payment p2_ 
        WHERE p2_.init <> 1 
        AND p2_.unpaid <> 1 
        AND p2_.cancel_date IS NULL 
        AND (p2_.value > 0 OR p2_.date BETWEEN '2011-03-23 00:00:00' AND '2011-03-23 23:59:59') 
        AND p2_.customer IS NOT NULL 
        GROUP BY p2_.customer
    )
) GROUP BY sclr1
ORDER BY sclr0 DESC
LIMIT 5

this make the query execution lasts forever (more than 2 minutes, had to kill MySQL processus). After some research, I found this answer to a similar problem, which do solve my problem in pure SQL. But I can't reproduce it in DQL. I tried :

$qb2 = $this->em->createQueryBuilder()
->select('sclr0, sclr1')
->from('('.$qb->getDQL().')', 't')
->orderBy('sclr0', 'DESC')
->setMaxResults(5);

which produces the following DQL :

SELECT sclr0, sclr1 FROM (
    SELECT COUNT(a) AS total, LOWER(a.city) AS city 
    FROM Evo\UserBundle\Entity\Address a 
    INNER JOIN EvoUserBundle:Customer c WITH c.customerAddress = a 
    WHERE a.city IS NOT NULL 
    AND a.city != '' 
    AND (
        c IN(
            SELECT IDENTITY(p.customer) 
            FROM Evo\SaleBundle\Entity\Payment p 
            WHERE p.init != 1 
            AND p.unpaid != 1 
            AND p.cancelDate IS NULL 
            AND (p.value > 0 OR p.date BETWEEN '2011-03-23 00:00:00' AND '2011-03-23 23:59:59') 
            AND p.customer IS NOT NULL 
            GROUP BY p.customer
        )
    ) GROUP BY city
) t ORDER BY sclr0 DESC

which returns the following error :

[Semantical Error] line 0, col 25 near '(SELECT COUNT(a)': Error: Class '(' is not defined. I read Doctrine2 doesn't support FROM subqueries because it wouldn't be able to build a result set mapping. But I have specific reasons to build this query in DQL and I can't use SQL for it. Any tip/workaround to make it work ?

Answer 1

use ResultSetMappingBuilder

Create your specific SQL and map it to entities

https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/native-sql.html