#24044: filtering by annotation executes sql function twice
----------------------------------------------+----------------------------
Reporter: Tim-Erwin | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.7
Severity: Normal | Keywords: annotate
| filter
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24044>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.f51ff78fca67a9640341ba7a3504c7e4%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
