Skip to content
Advertisement

Django how to find sum of reverse FK and reverse FK of the reverse FK

acw

Due to an absolutely fantastic DB design, I’ve stumbled across an issue. I’m counting the number of reverse FK matches and ITS reverse FK matches (following the method in this SO question: Django QuerySet ordering by number of reverse ForeignKey matches). I tried doing:

assert 6 == Model.objects.annotate(counting=Count(f"blah1") + Count(f"blah1__blah2")).get(id=1).counting

But I’m getting 6 == 7 as in my query is always giving me an extra 1. Why is that?

Edit: the models:

class Model(models.Model):
    ...

class Blah1(models.Model):
    parent = models.ForeignKey(Model, on_delete=models.CASCADE)

class Blah2(models.Model):
    parent = models.ForeignKey(Blah1, on_delete=models.CASCADE)

Advertisement

Answer

Count('blah1') will count the same number of items as Count('blah1__blah2'), because you made a JOIN. Indeed the query looks like:

SELECT model.*, COUNT(blah1.id) + COUNT(blah2.id) AS counting
FROM model
LEFT OUTER JOIN blah1.parent = model.id
LEFT OUTER JOIN blah2.parent = blah1.id

COUNT does not care about the values, or the values being duplicates, it only will not count NULLs, but for the rest, COUNT(blah1.id) and COUNT(blah2.id) will probably be nearly identical.

You thus should count unique values for blah1:

from django.db.models import Count

Model.objects.annotate(
    counting=Count('blah1', distinct=True) +
             Count('blah1__blah2')
).get(id=1).counting

Here we will thus only count the distinct blah1 objects.

Advertisement