Doctrine subquery within subquery-Collection of common programming errors

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.

The SQL version works exactly how I’d expect it to act. Converting to Doctrine gives me an error.

Here’s the original:

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

And here’s the broken Doctrine query:

Doctrine_Core::getTable("LotroRecord")
->createQuery("l")
->select("l.*")
->addSelect("DAY(created_at)")
->addSelect("MONTH(created_at)")
->addSelect("YEAR(created_at)")
->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")
->where("lotro_character_id = {$this->getId()}")
->groupBy("DAY(created_at)");

Which generates this SQL:

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) 

The error is:

Unknown column ‘infamydifference’ in ‘field list’

Any ideas?