Annotate a QuerySet with Exists instead of Count

Question

I have a model which contains a ManyToManyField:

class UserHasProduct(Model):
    user = ForeignKey(User, on_delete=CASCADE)
    products = ManyToManyField(Product)

class Product(Model):
    is_nfr = BooleanField(default=False)

I want to annotate my queryset with a simple is_nfr value that returns True if any of the products have is_nfr set to True. The best I could come up with is this:

UserHasProduct.objects.filter(user=self.request.user).annotate(
    is_nfr=Count("license_code_products", filter=Q(products__is_nfr=True))
)

That works, but instead of a boolean it returns an integer. Not a huge deal, but I am still wondering if it's possible to return a boolean, and if that would help with query performance in any way (i.e. it can stop as soon as it finds the first match, no idea if it works like that).

`UserHasProduct.objects.filter(user=self.request.user,products__is_nfr=True).exists()` you mean this? — Hemal Patel, Apr 20 '23 at 10:18
No, this would only result in rows where `is_nfr` is True. I do want to include the other rows as well, where it can be False. The real model has more fields, which I still want to get as well, even when the user owns only products for which `is_nfr` is False. — Kevin Renskers, Apr 20 '23 at 10:25
Sorry but I cant understand the requirement :'). you said `I want to annotate my queryset with a simple is_nfr value that returns True if any of the products have is_nfr set to True.` Above query will return True if any else there is no record with `is_nfr=True`. Maybe i'm wrong so can you explain a bit more? :) — Hemal Patel, Apr 20 '23 at 10:34
Your query filters out any `UserHasProduct` entries for which no products with `is_nfr`=True are found. That is not the same as annotating all results with a value. — Kevin Renskers, Apr 20 '23 at 11:27

score 1 · Answer 1 · answered Apr 20 '23 at 12:31

1

you can use conditional expression in Django (using Case & when )

from django.db.models import Q, Count, Case, When, Value,BooleanField

UserHasProduct.objects.filter(user=self.request.user).annotate(temp_num=Count("products", filter=Q(
    products__is_nfr=True))).annotate(is_nfr=Case(When(temp_num__gt=0, then=Value(True)), default=Value(False),outputField=BooleanField()))

and i found a similar question here Django query annotation with boolean field you can benefit from other answers as well

answered Apr 20 '23 at 12:31

Mina Naeem

41
2

Holy shit, that is a lot. I think I'll just stick with the count and cast that to a boolean in my serializer – Kevin Renskers Apr 20 '23 at 12:34
Well, that too will work fine – Mina Naeem Apr 20 '23 at 12:43

score 1 · Answer 2 · answered Apr 21 '23 at 06:21

You can use the Exists() subquery construct to perform the query, by referencing the join table:

UserHasProduct.objects.filter(...).annotate(
    is_nfr=Exists(UserHasProduct.products.objects.filter(
        product__is_nfr=True,
        userhasproduct=OuterRef('pk'),
    ))
)

This might perform slightly better when you have lots of related products, and most of them are is_nfr, because the database should be able to return as soon as it finds a matching row in the inner query.

Annotate a QuerySet with Exists instead of Count

2 Answers2