title: Doctrine subquery within subquery
body: <p>I'm trying to convert a raw mysql query to use doctrine. 
  The table is full of rows of statistics, and my query is checking to see how far from the average the stat gain has deviated from the average increase each day.</p>
  
  <p>The SQL version works exactly how I'd expect it to act. Converting to Doctrine gives me an error.</p>
  
  <p>Here's the original:</p>
  
  <pre><code>SELECT
      l.*,
      DAY(l.created_at) as day,
      MONTH(l.created_at) as month,
      YEAR(l.created_at) as year,
      (
          MAX(l.infamyrenown) -
          MIN(l.infamyrenown) -
          (
              SELECT AVG(infamydifference) as avginf FROM
              (
                      SELECT (
                          MAX(inf.infamyrenown) -
                          MIN(inf.infamyrenown)
                          ) as infamydifference
                      FROM lotro_record inf
                      GROUP BY DAY(inf.created_at)
              ) as p1
          )
      ) as infamy_deviance
  FROM
      lotro_record l
  GROUP BY
      year,month,day
  </code></pre>
  
  <p>And here's the broken Doctrine query:</p>
  
  <pre><code>Doctrine_Core::getTable("LotroRecord")
  -&gt;createQuery("l")
  -&gt;select("l.*")
  -&gt;addSelect("DAY(created_at)")
  -&gt;addSelect("MONTH(created_at)")
  -&gt;addSelect("YEAR(created_at)")
  -&gt;addSelect("(
      MAX(l.infamyrenown) -
      MIN(l.infamyrenown) -
      (
          select AVG(infamydifference) as avginf FROM (
              SELECT (
                  MAX(inf.infamyrenown) -
                  MIN(inf.infamyrenown)
              ) as infamydifference
              FROM LotroRecord inf
              GROUP BY DAY(inf.created_at)
          ) as p1
      )
  ) as infamy_deviance")
  -&gt;where("lotro_character_id = {$this-&gt;getId()}")
  -&gt;groupBy("DAY(created_at)");
  </code></pre>
  
  <p>Which generates this SQL:</p>
  
  <pre><code>SELECT l.id                                        AS l__id, 
         l.infamyrenown                              AS l__infamyrenown, 
         l.kills                                     AS l__kills, 
         l.killing_blows                             AS l__killing_blows, 
         l.kills_above_rating                        AS l__kills_above_rating, 
         l.kills_below_rating                        AS l__kills_below_rating, 
         l.deaths                                    AS l__deaths, 
         l.lotro_character_id                        AS l__lotro_character_id, 
         l.created_at                                AS l__created_at, 
         l.updated_at                                AS l__updated_at, 
         DAY(l.created_at)                           AS l__0, 
         MONTH(l.created_at)                         AS l__1, 
         YEAR(l.created_at)                          AS l__2, 
         ( Max(l.infamyrenown) - Min(l.infamyrenown) - (SELECT 
           Avg(infamydifference) AS avginf 
                                                        FROM 
             (SELECT ( Max(l2.infamyrenown) - Min(l2.infamyrenown) ) AS l__0 
              FROM   lotro_record l2 
              GROUP  BY DAY(l2.created_at)) AS p1) ) AS l__3 
  FROM   lotro_record l 
  WHERE  ( l.lotro_character_id = 1 ) 
  GROUP  BY DAY(l.created_at) 
  </code></pre>
  
  <p>The error is:</p>
  
  <p>Unknown column 'infamydifference' in 'field list'</p>
  
  <p>Any ideas?</p>