id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
24044	filtering by annotation executes sql function twice	Tim-Erwin	nobody	"I have two models A and B connected by a ManyToOne relation (A has many Bs). When I filter A by the number of related Bs
{{{
A.objects.annotate(myCount=Count('bs')).filter(myCount__gt=2)
}}}

Django creates a (my)SQL query like so:

{{{
SELECT `a`.`id`, `a`.`name`, COUNT(`b`.`id`) AS `myCount` FROM `a` LEFT OUTER JOIN `b` ON ( `a`.`id` = `b`.`a_id` ) GROUP BY `a`.`id` HAVING COUNT(`b`.`id`) > 2 ORDER BY NULL LIMIT 21;
}}}

In principle this works, but COUNT is executed twice instead of using the alias 'myCount'. This might not matter in this particular example but I have a custom SQL function that I want to use the same way. It is much more costly and thus the query execution time is basically doubled.

I cannot see any scenario where executing the function twice is of any benefit. I'd suggest to fix this so the alias is used in the HAVING clause."	Cleanup/optimization	closed	Database layer (models, ORM)	dev	Normal	needsinfo	annotate filter	josh.smeaton@…	Accepted	0	0	0	0	0	0