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

philipphoffmann · Answer 1 · 2012-10-21T08:05:59.453

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/

score 1 · Accepted Answer · answered Dec 19 '11 at 16:48

You can't have plain SQL in your doctrine subqueries. You have two choices:

Use the model name for subscribers_years table in your subquery
Use RAW SQL in your query

If you don't need any advanced SQL I'd go with option 1, since it has less SQL and you don't need to specify join conditions otherwise you should use 2.

You can always add a Doctrine class for your subscribers_years if you don't have it.

Doctrine Query calculate MAX value in WHERE clause

Solution

2 Answers2