[Fixed]-Annotate django query if filtered row exists in second table

21👍

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

8👍

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.

👤Flimm

0👍

Easy approach, be careful with performance:

pk_pices = ( User_Piece
            .objects
            .filter(user=user)
            .distinct()
            .values_list( 'id', flat=True)
           )
pieces = pieces.objects.filter( id__in = pk_pieces )

Also, notice that you have a n:m relation ship, you can rewrite models as:

class Piece(models.Model):
    cost = models.IntegerField(default=50)
    piece = models.CharField(max_length=256)
    users = models.ManyToManyField(User, through='User_Piece',  #<- HERE!
                                   related_name='Pieces')       #<- HERE!

And get user pieces as:

pieces = currentLoggedUser.pieces.all()

Leave a comment