title: SELECT [...] FROM (SELECT [...]) in DQL
body: <p>I have the following query :</p>
  
  <pre><code>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 &lt;&gt; '' 
  AND (
      c1_.id IN (
          SELECT p2_.customer AS sclr2 
          FROM payment p2_ 
          WHERE p2_.init &lt;&gt; 1 
          AND p2_.unpaid &lt;&gt; 1 
          AND p2_.cancel_date IS NULL 
          AND (p2_.value &gt; 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
  </code></pre>
  
  <p>I built with the following DQL : </p>
  
  <pre><code>$qb = $this-&gt;createQueryBuilder('a')
  -&gt;select('COUNT(a) AS total, LOWER(a.city) AS city')
  -&gt;join('EvoUserBundle:Customer', 'c', 'WITH', 'c.customerAddress = a')
  -&gt;where('a.city IS NOT NULL')
  -&gt;andWhere('a.city != \'\'')
  -&gt;groupBy('city')
  -&gt;setMaxResults(5);
  
  $subquery = $this-&gt;em-&gt;getRepository('EvoSaleBundle:Payment')-&gt;createQueryBuilder('p')
  -&gt;select('IDENTITY(p.customer)')
  -&gt;andWhere('p.init != 1')
  -&gt;andWhere('p.unpaid != 1')
  -&gt;andWhere('p.cancelDate IS NULL')
  -&gt;andWhere('p.value &gt; 0 OR p.date BETWEEN \'2011-03-23 00:00:00\' AND \'2011-03-23 23:59:59\'')
  -&gt;andWhere('p.customer IS NOT NULL')
  -&gt;groupBy('p.customer');
  
  $qb-&gt;andWhere($qb-&gt;expr()-&gt;in('c', $subquery-&gt;getDQL()));
  </code></pre>
  
  <p>and returns the following data set :</p>
  
  <pre><code>array(10) {
    [0]=&gt;
    array(2) {
      ["total"]=&gt;
      string(1) "3"
      ["city"]=&gt;
      string(18) "epinay sur seine"
    }
    [1]=&gt;
    array(2) {
      ["total"]=&gt;
      string(1) "7"
      ["city"]=&gt;
      string(8) "firfol"
    }
    [2]=&gt;
    array(2) {
      ["total"]=&gt;
      string(1) "2"
      ["city"]=&gt;
      string(20) "fontenay sous bois"
    }
    [3]=&gt;
    array(2) {
      ["total"]=&gt;
      string(1) "1"
      ["city"]=&gt;
      string(13) "puyravault"
    }
    [4]=&gt;
    array(2) {
      ["total"]=&gt;
      string(1) "3"
      ["city"]=&gt;
      string(8) "torcy"
    }
  }
  </code></pre>
  
  <p>The problem appears when I add an ORDER BY clause to the query : </p>
  
  <pre><code>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 &lt;&gt; '' 
  AND (
      c1_.id IN (
          SELECT p2_.customer AS sclr2 
          FROM payment p2_ 
          WHERE p2_.init &lt;&gt; 1 
          AND p2_.unpaid &lt;&gt; 1 
          AND p2_.cancel_date IS NULL 
          AND (p2_.value &gt; 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
  </code></pre>
  
  <p>this make the query execution lasts forever (more than 2 minutes, had to kill MySQL processus). After some research, I found <a href="https://stackoverflow.com/questions/884661/slow-query-when-using-order-by/884678#884678">this answer</a> to a similar problem, which do solve my problem in pure SQL. But I can't reproduce it in DQL. I tried :</p>
  
  <pre><code>$qb2 = $this-&gt;em-&gt;createQueryBuilder()
  -&gt;select('sclr0, sclr1')
  -&gt;from('('.$qb-&gt;getDQL().')', 't')
  -&gt;orderBy('sclr0', 'DESC')
  -&gt;setMaxResults(5);
  </code></pre>
  
  <p>which produces the following DQL :</p>
  
  <pre><code>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 &gt; 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
  </code></pre>
  
  <p>which returns the following error : </p>
  
  <p><code>[Semantical Error] line 0, col 25 near '(SELECT COUNT(a)': Error: Class '(' is not defined</code>. I read <a href="http://www.doctrine-project.org/jira/browse/DDC-2793" rel="nofollow noreferrer">Doctrine2 doesn't support FROM subqueries because it wouldn't be able to build a result set mapping.</a>
  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 ?</p>