#10060: Multiple table annotation failure
-------------------------------------+-------------------------------------
Reporter: svsharma@… | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by akermen):
No sure this will work for all different cases, at least it worked well
for me and the below simplified test case:
Model definitions of test case,
{{{
class ModelA(models.Model):
title = models.CharField(max_length=255, blank=False, null=False,
unique=True)
class ModelB(models.Model):
parent = models.ForeignKey(ModelA, on_delete=models.CASCADE)
value = models.IntegerField()
class ModelC(models.Model):
parent = models.ForeignKey(ModelA, on_delete=models.CASCADE)
}}}
and sample data:
{{{
instance_a_1 = ModelA(title="instance1")
instance_a_1.save()
instance_b = ModelB(parent=instance_a_1, value=1)
instance_b.save()
instance_b = ModelB(parent=instance_a_1, value=3)
instance_b.save()
instance_b = ModelB(parent=instance_a_1, value=5)
instance_b.save()
instance_c = ModelC(parent=instance_a_1)
instance_c.save()
instance_c = ModelC(parent=instance_a_1)
instance_c.save()
instance_c = ModelC(parent=instance_a_1)
instance_c.save()
instance_c = ModelC(parent=instance_a_1)
instance_c.save()
instance_c = ModelC(parent=instance_a_1)
instance_c.save()
instance_a_2 = ModelA(title="instance2")
instance_a_2.save()
instance_b = ModelB(parent=instance_a_2, value=7)
instance_b.save()
instance_b = ModelB(parent=instance_a_2, value=11)
instance_b.save()
instance_c = ModelC(parent=instance_a_2)
instance_c.save()
instance_c = ModelC(parent=instance_a_2)
instance_c.save()
instance_c = ModelC(parent=instance_a_2)
instance_c.save()
}}}
Trying to get two independent annotations from two different tables (sum
of values from ModelB and number of ModelC instances per ModelA instance):
{{{
for a in ModelA.objects.all() \
.annotate(sumB=Sum('modelb__value')) \
.annotate(countC=Count('modelc', distinct=True)):
print "%s: sumB: %s countC: %s" % (a.title, a.sumB, a.countC)
}}}
we get these results
{{{
instance1: sumB: 45 countC: 5
instance2: sumB: 54 countC: 3
}}}
instead of these:
{{{
instance1 sumB: 9 countC: 5
instance2 sumB: 18 countC: 3
}}}
no surprise until here.
As you can see the 'sumB' values are repeated (multiplied) by a factor,
lets account that factor inside single query:
{{{
for a in ModelA.objects.all() \
.annotate(countC=Count('modelc', distinct=True)) \
.annotate(countB=Count('modelb')) \
.annotate(countB_distinct=Count('modelb', distinct=True)) \
.annotate(sumB_multiplied=Sum('modelb__value')) \
.annotate(sumB=(F('sumB_multiplied') * F('countB_distinct')) /
F('countB')):
print "%s sumB: %s countC: %s" % (a.title, a.sumB, a.countC)
}}}
and get correct values:
{{{
instance1 sumB: 9 countC: 5
instance2 sumB: 18 countC: 3
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/10060#comment:60>
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/081.ce586e4a14d263f3156119323db6e639%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
