The documentation advises against combining multiple annotations in one query:
https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#combining-multiple-aggregations
due to the infamous bug #10060: https://code.djangoproject.com/ticket/10060
And indeed, having tried the counter-example from the documentation, I can
easily reproduce the bug.
However... To my astonishment... I produced a **working** example of combining
multiple annotations. An example that, IIUC the aforementioned docu and bug
report, should **not** be working.
Not that I'm complaining, actually my life has just became much, MUCH easier
thanks to this query working... I only can't understand WHY is it working?
Here's my case: (not minimalised too much to improve the chances of failure):
file models.py:
from django.db.models import Model, CharField, PositiveSmallIntegerField,
PositiveIntegerField, ForeignKey, CASCADE
# Create your models here.
class Province(Model):
name = CharField(max_length=64, primary_key=True)
class Municipality(Model):
name = CharField(max_length=64, primary_key=True)
province = ForeignKey(Province, on_delete=CASCADE)
class Ward(Model):
no=PositiveSmallIntegerField()
municipality = ForeignKey(Municipality, on_delete=CASCADE)
spoiled_votes = PositiveIntegerField()
did_not_vote = PositiveIntegerField()
class Meta:
unique_together = ('no', 'municipality')
class Candidate(Model):
first_name = CharField(max_length=64)
last_name = CharField(max_length=64)
class Meta:
unique_together=('first_name', 'last_name')
class Votes(Model):
ward = ForeignKey(Ward, on_delete=CASCADE)
candidate = ForeignKey(Candidate, on_delete=CASCADE)
amount = PositiveIntegerField()
class Meta:
unique_together = ('ward', 'candidate')
File tests.py:
from django.test import TestCase
from random import seed, randrange
from worksapp.models import Province, Municipality, Ward, Candidate, Votes
from django.db.models import Subquery, Sum, OuterRef, PositiveIntegerField
from operator import attrgetter
# Create your tests here.
class AnnotateTest(TestCase):
def test(self):
seed(1)
provinces = [
Province(name='Province'+str(i))
for i in range(0, 10)
]
Province.objects.bulk_create(provinces)
municipalities = [
Municipality(name='Municipality'+str(i), province=provinces[i//10])
for i in range(0, 100)
]
Municipality.objects.bulk_create(municipalities)
wards = [
Ward(
no=i%10, municipality=municipalities[i//10], pk=i,
spoiled_votes=randrange(0, 100), did_not_vote=randrange(0, 1000)
) for i in range(0, 1000)
]
Ward.objects.bulk_create(wards)
candidates = [
Candidate(first_name='name'+str(i), last_name='surname'+str(i),
pk=i)
for i in range(0, 10)
]
Candidate.objects.bulk_create(candidates)
votes = [
Votes(ward=wards[i//10], candidate=candidates[i%10],
amount=randrange(0, 100), pk=i)
for i in range(0, 10000)
]
Votes.objects.bulk_create(votes)
multiple_annotate = Municipality.objects.annotate(
cand3votes=Subquery(
Votes.objects.filter(
candidate__first_name='name3',
candidate__last_name='surname3',
ward__municipality=OuterRef('pk')
).values('ward__municipality').annotate(res=Sum('amount')).values('res'),
output_field=PositiveIntegerField()
),
cand7votes=Subquery(
Votes.objects.filter(
candidate__first_name='name7',
candidate__last_name='surname7',
ward__municipality=OuterRef('pk')
).values('ward__municipality').annotate(res=Sum('amount')).values('res'),
output_field=PositiveIntegerField()
),
spoiled_votes=Sum('ward__spoiled_votes'),
votes_not_cast=Sum('ward__did_not_vote')
).get(name='Municipality'+str(83))
self.assertEqual(multiple_annotate.cand3votes,
sum(map(attrgetter('amount'), votes[8303:8403:10])))
self.assertEqual(multiple_annotate.cand7votes,
sum(map(attrgetter('amount'), votes[8307:8407:10])))
self.assertEqual(multiple_annotate.spoiled_votes,
sum(map(attrgetter('spoiled_votes'), wards[830:840])))
self.assertEqual(multiple_annotate.votes_not_cast,
sum(map(attrgetter('did_not_vote'), wards[830:840])))
multiple_annotate = Province.objects.annotate(
cand0votes=Subquery(
Votes.objects.filter(
candidate__first_name='name0',
candidate__last_name='surname0',
ward__municipality__province=OuterRef('pk')
).values('ward__municipality__province').annotate(res=Sum('amount')).values('res'),
output_field=PositiveIntegerField()
),
cand9votes=Subquery(
Votes.objects.filter(
candidate__first_name='name9',
candidate__last_name='surname9',
ward__municipality__province=OuterRef('pk')
).values('ward__municipality__province').annotate(res=Sum('amount')).values('res'),
output_field=PositiveIntegerField()
),
spoiled_votes=Sum('municipality__ward__spoiled_votes'),
votes_not_cast=Sum('municipality__ward__did_not_vote')
).get(name='Province5')
self.assertEqual(multiple_annotate.cand0votes,
sum(map(attrgetter('amount'), votes[5000:6000:10])))
self.assertEqual(multiple_annotate.cand9votes,
sum(map(attrgetter('amount'), votes[5009:6009:10])))
self.assertEqual(multiple_annotate.spoiled_votes,
sum(map(attrgetter('spoiled_votes'), wards[500:600])))
self.assertEqual(multiple_annotate.votes_not_cast,
sum(map(attrgetter('did_not_vote'), wards[500:600])))
To my astonishement the test PASSES. Why?
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/VI1PR03MB1006C4B4D7252F69956C99C7D9890%40VI1PR03MB1006.eurprd03.prod.outlook.com.
For more options, visit https://groups.google.com/d/optout.
