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
24766	annotation with Q object filters results	karyon	nobody	"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"" )'
}}}"	Bug	closed	Database layer (models, ORM)	1.8	Normal	fixed		josh.smeaton@… python@…	Accepted	0	0	0	0	0	0