pythondjangopostgresqlannotationsdjango-annotate

Django boolean annotate returning duplicates


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?


Solution

  • 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.