#24766: annotation with Q object filters results
-------------------------------------+-------------------------------------
Reporter: karyon | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by karyon):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> I tried to annotate a query of UserProfiles with a boolean indicating
> whether each user is in a specific group or not. Besides the fact that i
> didn't find any obvious or easy to find solution for that, i encountered
> the following oddity:
>
> Any of these:
>
> {{{
> UserProfile.objects.annotate(is_member=Case(When(groups__name="Group
> name", then=Value(True)), default=Value(False),
> output_field=BooleanField()))
> }}}
> {{{
> UserProfile.objects.annotate(is_member=ExpressionWrapper(Q(groups__name="Group
> name"), output_field=BooleanField()))
> }}}
>
> returns only users that are in some group. Users without groups are not
> in the result. This was unexpected since i didn't request any filtering.
>
> The issue might be that both queries use inner joins. here's the sql:
> {{{
> 'SELECT <snip>, CASE WHEN "auth_group"."name" = Group name THEN True ELSE
> False END AS "is_member" FROM "evaluation_userprofile" INNER JOIN
> "evaluation_userprofile_groups" ON ( "evaluation_userprofile"."id" =
> "evaluation_userprofile_groups"."userprofile_id" ) INNER JOIN
> "auth_group" ON ( "evaluation_userprofile_groups"."group_id" =
> "auth_group"."id" )'
> }}}
> {{{
> 'SELECT <snip>, "auth_group"."name" = Group name AS "is_member" FROM
> "evaluation_userprofile" INNER JOIN "evaluation_userprofile_groups" ON (
> "evaluation_userprofile"."id" =
> "evaluation_userprofile_groups"."userprofile_id" ) INNER JOIN
> "auth_group" O
> N ( "evaluation_userprofile_groups"."group_id" = "auth_group"."id" )'
> }}}
New description:
I tried to annotate a query of UserProfiles with a boolean indicating
whether each user is in a specific group or not. Besides the fact that i
didn't find any obvious or easy to find solution for that, i encountered
the following oddity:
Any of these:
{{{
UserProfile.objects.annotate(is_member=Case(When(groups__name="Group
name", then=Value(True)), default=Value(False),
output_field=BooleanField()))
}}}
{{{
UserProfile.objects.annotate(is_member=ExpressionWrapper(Q(groups__name="Group
name"), output_field=BooleanField()))
}}}
returns only users that are in some group. Users without groups are not in
the result. This was unexpected since i didn't request any filtering.
The issue might be that both queries use inner joins. here's the sql:
{{{
'SELECT <snip>, CASE WHEN "auth_group"."name" = Group name THEN True ELSE
False END AS "is_member" FROM "evaluation_userprofile" INNER JOIN
"evaluation_userprofile_groups" ON ( "evaluation_userprofile"."id" =
"evaluation_userprofile_groups"."userprofile_id" ) INNER JOIN "auth_group"
ON ( "evaluation_userprofile_groups"."group_id" = "auth_group"."id" )'
}}}
{{{
'SELECT <snip>, "auth_group"."name" = Group name AS "is_member" FROM
"evaluation_userprofile" INNER JOIN "evaluation_userprofile_groups" ON (
"evaluation_userprofile"."id" =
"evaluation_userprofile_groups"."userprofile_id" ) INNER JOIN "auth_group"
ON ( "evaluation_userprofile_groups"."group_id" = "auth_group"."id" )'
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/24766#comment:1>
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/064.aa1a765aea7074f4cb567d95a017983e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
