#30011: Count with filter annotation bug on filter
-------------------------------------+-------------------------------------
Reporter: Taqi Abbas | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Annotation, Count, | Triage Stage:
Filter | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Taqi Abbas):
Hey Simon,
Your suggested code also breaks. This is the line that invalidates the
sql.
{{{
queryset.filter(id__in=filtered_queryset.values('id'))
}}}
As for the cross joins issue, this is the code that I basically use to
remove duplicates.
{{{
if hasattr(queryset.model, 'id'):
return queryset.filter(id__in=filtered_queryset.values('id'))
else:
return filtered_queryset.distinct(*view.get_ordering(request))
}}}
its done like this because distinct fails for a model that has a jsonb
field. So I just do this.
I have made a github repo with a testcase to reproduce it. Heres the
link[https://github.com/taqi457/Django-Count-TestCase]:
This a simpler query reproduced from the repo above, hope this helps.
{{{
queryset = Ticket.objects.filter(is_deleted=False)\
.annotate(commit_count=Count('commit_set', filter=Q(is_deleted=False)))
filtered_queryset = queryset.filter(heading__icontains='')
result = queryset.filter(id__in=filtered_queryset.values('id'))
print(result.query)]
SELECT
"test_count_ticket"."id",
"test_count_ticket"."uuid",
"test_count_ticket"."created_on",
"test_count_ticket"."updated_on",
"test_count_ticket"."server_created_on",
"test_count_ticket"."server_updated_on",
"test_count_ticket"."is_deleted",
"test_count_ticket"."heading",
Count("test_count_commit"."id",
"test_count_ticket"."is_deleted" = false, "test_count_ticket"."is_deleted"
= false) filter (WHERE "test_count_ticket"."is_deleted" = false) AS
"commit_count"
FROM "test_count_ticket"
LEFT OUTER JOIN "test_count_commit"
ON (
"test_count_ticket"."id" =
"test_count_commit"."ticket_id")
WHERE (
"test_count_ticket"."is_deleted" = false
AND "test_count_ticket"."id" IN
(
SELECT u0."id"
FROM
"test_count_ticket" u0
LEFT OUTER JOIN
"test_count_commit" u1
ON (
u0."id" = u1."ticket_id")
WHERE (
u0."is_deleted" = false
AND
upper(u0."heading"::text) LIKE upper(%%))
GROUP BY u0."id"))
GROUP BY "test_count_ticket"."id"
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30011#comment:2>
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/065.29ce89d04d6d653995b3340ea6f42fbb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
