Doctrine and aliases from the select clause in the where clause.

Question

I am using an alias in my select clause ("AVG(u.rating) as avg_rating") and would then like to add this in my where clause "avg_rating > 3" but when I try and run this query I get a "Unknown column 'u3__1' in 'where clause'." Does anyone happen to know how I can get my where clause to see the alias? This alias works in the orderBy with no issue, just not the where.

Thank you.

EDIT: (for more details)

The above was an example, but here is the real rendered SQL, not as simple. My issue is actually with an alias on value with a bunch of conditionals. And the alias I am having trouble with is the generated date which has recurrences and doesn't have to every date field populated. So I am posting a simple SQL query that gives me the same issue.

SELECT t.type_id as type_alias, t.* FROM theme as t WHERE t.id > 1 AND type_alias = 3

And here is the real query if you are so interested:

SELECT t.id AS t__id, t.created_by AS t__created_by, t.type_id AS t__type_id, t.url_slug AS t__url_slug, t.name AS t_name, t.description AS t_description, t.summary AS t__summary, t.start_month AS t__start_month, t.start_day AS t__start_day, t.start_year AS t__start_year, t.duration_unit AS t__duration_unit, t.duration_length AS t__duration_length, t.is_active AS t__is_active, t.is_public AS t__is_public, t.needs_moderation AS t__needs_moderation, t.recurrence AS t__recurrence, t.tag_string AS t__tag_string, t.date_created AS t__date_created, t.date_updated AS t__date_updated, AVG(t2.rating) AS t2__0, IF(t.recurrence = "none", STR_TO_DATE(CONCAT(t.start_month, t.start_day, t.start_year), "%m%d%Y"), (IF(STR_TO_DATE(CONCAT(t.start_month, t.start_day, YEAR(NOW())), "%m%d%Y") > NOW(), STR_TO_DATE(CONCAT(t.start_month, t.start_day, YEAR(NOW())), "%m%d%Y"), STR_TO_DATE(CONCAT(t.start_month, t.start_day, (YEAR(NOW())+1)), "%m%d%Y")))) AS t_1, (COUNT(u.id) + COUNT(e.id)) AS u_2 FROM theme t LEFT JOIN theme_rating t2 ON t.id = t2.theme_id LEFT JOIN user_saves_themes u ON t.id = u.theme_id LEFT JOIN event e ON ((e.is_active = 1 AND e.theme_id = t.id)) WHERE t.id IN ('3', '2', '1') AND (IF(t.recurrence = "none", STR_TO_DATE(CONCAT(t.start_month, t.start_day, t.start_year), "%m%d%Y"), (IF(STR_TO_DATE(CONCAT(t.start_month, t.start_day, YEAR(NOW())), "%m%d%Y") > NOW(), STR_TO_DATE(CONCAT(t.start_month, t.start_day, YEAR(NOW())), "%m%d%Y"), STR_TO_DATE(CONCAT(t.start_month, t.start_day, (YEAR(NOW())+1)), "%m%d%Y")))) >= FROM_UNIXTIME(1278001295) AND t.is_public = ? AND t.is_active = ?) GROUP BY t.id ORDER BY t__1

Answer 1

Try having instead of where.