Django annotate relation with specific object

Question

I have two models in my Django database:

class Member(models.Model):
    signed_up_to=models.ManyToManyField(Piece)
class Piece(models.Model):
    title = models.CharField(max_length=200,null=True,blank=True)

When I pull Piece objects from my database, I would like to annotate (Bool or Integer) whether or not the Piece appears in signed_up_to of a specific Member. I've looked around for an answer all over the internet, but with no success. Hope there's anyone who can help me!

willeM_ Van Onsem · Accepted Answer · 2020-06-13 20:25:12Z

2

Given the Member object is member, you can make use of an Exists subquery [Django-doc]:

from django.db.models import Exists, OuterRef

Piece.objects.annotate(
    has_member=Exists(
        Member.signed_up_to.through.objects.filter(
            piece_id=OuterRef('pk'),
            member_id=member.pk
        )
    )
)

So has_member will be True if member has this Piece object in its signed_up_to relation, and False otherwise.

answered Jun 13, 2020 at 20:25

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jelle Over a year ago

Perfect! Dankjewel Willem!

revliscano Over a year ago

Oh, for Christ's sake. Man, I admire you so bad. This is perfect... Could you please share with me what's the need of using OuterRef? I just don't get it, because the docs say that you should use it if you're going to refer a field from the subquery, but in your example (and in the example used in the docs) I don't see that usage. @Willem

willeM_ Van Onsem Over a year ago

@revliscano: it means we take the pk (so the id field) of the outer query of this subquery, so the id of the Piece object.

Blackdoor Over a year ago

@revliscano Exists is a subclass of Subquery.

Collectives™ on Stack Overflow

Django annotate relation with specific object

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related