Annotate django query if filtered row exists in second table

Question

I have two tables (similar to the ones below):

class Piece(models.Model):
    cost = models.IntegerField(default=50)
    piece = models.CharField(max_length=256)

class User_Piece (models.Model):
    user = models.ForeignKey(User)
    piece = models.ForeignKey(Piece)

I want to do a query that returns all items in Piece, but annotates each row with whether or not the logged in user owns that piece (so there exists a row in User_Piece where user is the logged in user).

I tried:

pieces = Piece.objects.annotate(owned=Count('user_piece__id'))

But it puts a count > 0 for any piece that is owned by any user. I'm not sure where/how I put in the condition that the user_piece must have the specified user I want. If I filter on user__piece__user=user, then I don't get all the rows from Piece, only those that are owned.

Answer 1

You could use Exist subquery wrapper:

from django.db.models import Exists, OuterRef

subquery = User_Piece.objects.filter(user=user, piece=OuterRef('pk'))
Piece.objects.annotate(owned=Exists(subquery))

https://docs.djangoproject.com/en/dev/ref/models/expressions/#exists-subqueries

Answer 2

In newer versions of Django, you can do:

from django.db.models import Exists, OuterRef

pieces = Piece.objects.annotate(
   owned=Exists(UserPiece.objects.filter(piece=OuterRef('id'), user=request.user))
)

for piece in pieces:
    print(piece.owned) # prints True or False

Of course, you can replace the name owned with any name you want.