We're encountering a very strange problem regarding the negation of Q objects in Django. Let's just use Football as the example:
class Team(models.Model):
id = UUIDField(primary_key=True)
class Player(models.Model):
id = UUIDField(primary_key=True)
name = models.CharField(max_length=128)
team = models.ForeignKey(Team)
touchdowns = models.IntegerField()
There are 10 teams.
There are 100 players, with 10 on each team. Each team has a player named "Joe". There is one "Joe" on one team who has scored 5 touchdowns. All other Joe's have scored 1 touchdown. There are 8 teams where every Player has scored only 1 touchdown.
I want to get the Teams that have a player named Joe that has scored at least 3 Touchdowns.
models.Team.objects.filter(Q(player__name="Joe", player__touchdowns__gte=3)).count()
That returns One, as it should.The negation of that should return 9 (The other 9 teams that don't have a player named Joe that has at least 3 Touchdowns):
models.Team.objects.filter(~Q(player__name="Joe", player__touchdowns__gte=3)).count()
instead returns any team where everyone on that team has less than 3 Touchdowns (8).
Where am I going wrong? Please note that our actual application of this is much more complicated, so we NEED to use Q objects with negation, we cannot use Exclude.
The best way to ferret out why these differences occur is to investigate the queries that are generated: django-debug-toolbar comes with a debugsqlshell command that prints the actual query sent to the database after any use of the Django queryset API. For these tests, I used the User model with a join on Group. I too noticed different counts for selected objects, so on the face it seems like a good correlation to your use-case.
User.objects.filter(~Q(username='jdoe', groups__name='Awesome Group'))
SELECT "auth_user"."id",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."password",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."is_superuser",
"auth_user"."last_login",
"auth_user"."date_joined"
FROM "auth_user"
WHERE NOT ("auth_user"."username" = 'jdoe'
AND "auth_user"."id" IN
(SELECT U1."user_id"
FROM "auth_user_groups" U1
INNER JOIN "auth_group" U2 ON (U1."group_id" = U2."id")
WHERE (U2."name" = 'Awesome Group'
AND U1."user_id" IS NOT NULL))) LIMIT 21
User.objects.exclude(Q(username='jdoe', groups__name='Awesome Group'))
SELECT "auth_user"."id",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."password",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."is_superuser",
"auth_user"."last_login",
"auth_user"."date_joined"
FROM "auth_user"
INNER JOIN "auth_user_groups" ON ("auth_user"."id" = "auth_user_groups"."user_id")
INNER JOIN "auth_group" ON ("auth_user_groups"."group_id" = "auth_group"."id")
WHERE NOT (("auth_user"."username" = 'jdoe'
AND "auth_group"."name" = 'Awesome Group')) LIMIT 21
The difference here comes in where the INNER JOIN happens. The Q object causes the INNER JOIN in the first example and then the selection with the INNER JOIN is negated because of the ~. The case of exclude, the negation happens in parallel to the INNER JOIN.