I have the following models:
class Institution(models.Model):
pass
class Headquarter(models.Model):
institution = models.ForeignKey(Institution, related_name='headquarters')
class Audit(models.Model):
headquarter = models.ForeignKey(Headquarter, related_name='audits')
And the following query (basically, if an institution has at least one audit then has_visits must be true:
Institution.objects.annotate(
has_visits=models.Case(
models.When(headquarters__audits=None, then=False),
default=True,
output_field=models.BooleanField()
)
)
The problem is that if an institution has 2 audits then the queryset returns duplicate rows. I imagine it has something to do with the joins at the SQL level but I'm not sure how to correct it. I found this answer but I don't think OuterRef is what I'm looking for in my situation. What's the right way to accomplish this?
You can work with an Exists
subquery [Django-doc]:
from django.db.models import Exists, OuterRef
Institution.objects.annotate(
has_visits=Exists(
Audit.objects.filter(headquarter__institution=OuterRef('pk'))
)
)
Something else that could help is to let the duplicates "collapse", for example with a Max
:
from django.db.models import Max, Value
from django.db.models.functions import Coalesce
Institution.objects.annotate(
has_visits=Coalesce(Max(models.Case(
models.When(headquarters__audits=None, then=False),
default=True,
output_field=models.BooleanField()
)), Value(False))
)
but that likely makes it less readable and less efficient.