Doctrine Query calculate MAX value in WHERE clause

Question

I have this tables:

subscribers

id | name  |
---+-------+
1  | Hill  |
2  | Sam   |
3  | Joe   |

subscribers_years

subscriber_id | year  |
--------------+-------+
1             | 2009  |
1             | 2010  |
1             | 2011  |
2             | 2009  |
2             | 2010  |
3             | 2010  |

I want to take an advanced search using only records that have a maximum as x. If x = 2010 Results query must be: Sam and Joe!

I try this:

$query = $this->createQuery('su')
            ->select('su.id, su.name)
            ->addWhere(
                '(SELECT MAX(year) AS maxYear FROM `subscribers_years` WHERE `subscriber_id`=`su.id`) = ?',
                2010
            );

but i have the message error: Message: Couldn't find class subscribers_years

how should I write my query right way?

---

Solution

$query->addWhere('(SELECT MAX(year) AS maxYear FROM SubscriberYear WHERE subscriber_id=su.id) = ?', 2010 );

my mistake was that I did not use the class name Doctrine as the From, but used the name of the table mysql! :S

Answer 1

I think the best way to go here is using createSubquery() to explicitly tell doctrine about the subquery, then nest it into the where condition. The pattern is pretty easy and can be used for any complexity. You query should then look something like this:

$query = $this->createQuery('su')
    ->select('su.id, su.name)
;

$subquery = $query->createSubquery()
    ->select("MAX(year) AS maxYear")
    ->from("SubscriberYear")
    ->where("subscriber_id=su.id")
;

$query->addWhere($subquery->getDql().' = ?', 2010 );

Here is another example for creating Subqueries:

http://www.philipphoffmann.de/2012/08/taming-doctrine-subqueries/