djangodjango-orm

Annotate and filter by related model


I have two models:

class ABSLoyaltyCard(models.Model):
    title = models.CharField(
        max_length=120,
        blank=True,
    )

class CardToOwner(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    card_frame = models.ForeignKey(
        ABSLoyaltyCard,
        on_delete=models.CASCADE,
        related_name="clients",
    )
    owner = models.ForeignKey(
        Guest,
        on_delete=models.CASCADE,
        related_name="cards",
    )

For example I have owner - Bob(CardToOwner.owner).

I need to get all cards from ABSLoyaltyCard, with annotated field 'is_my_card', where 'is_my_card' = True if ABSLoyaltyCard is in CardToOwner with owner = Bob, otherwise - False.

What's the good way to do it?


Solution

  • You can use an Exists expression [Django-doc] with:

    from django.db.models import OuterRef, Exists
    
    ABSLoyaltyCard.objects.annotate(
        is_my_card=Exists(
            CardToOwner.objects.filter(
                card_frame=OuterRef('pk'), owner=Bob
            )
        )
    )

    Where Bob is thus a Guest object, or the primary key of a Guest object.