How to Django queryset annotate True when all BooleanField of related objects are True else False?

Question

I have a model who looks like this :

class Test(models.Model):
    user = models.ForeignKey('users.CustomUser', models.CASCADE)
    name = models.CharField(max_length=64)


class TestVersion(models.Model):
    test = models.ForeignKey('Test', models.CASCADE)
    name = models.CharField(max_length=255)
    validation_1 = models.BooleanField(default=False, editable=False)
    validation_2 = models.BooleanField(default=False, editable=False)
    validation_3 = models.BooleanField(default=False, editable=False)
    validation_4 = models.BooleanField(default=False, editable=False)

Sometimes i have like hundreds of TestVersion linked to a Test.

And I want something like :

user_test = Test.objects.filter(
    user=request.user
).annotate(
    number_of_test=Count('testversion', distinct=True),
    all_validation_1="True or False ?", # if all testversion_set.all() of the current test are True, return True else False.
    all_validation_2="True or False ?", # same
    all_validation_3="True or False ?", # same
    all_validation_4="True or False ?", # same
).distinct()

# I Want for example :
test_1 = user_test.first()
test_1_validation_1 = test_1.testversion_set.all().count()
test_1_validation_1_true = test_1.testversion_set.filter(validation_1=True).count()
all_validation_1 = test_1_validation_1 == test_1_validation_true
test_1.all_validation_1 == all_validation_1 # True

# Or something like :
test_1 = user_test.first()
all_validation_1 = all(test_1.testversion_set.all().values_list('validation_1', flat=True))
test_1.all_validation_1 == all_validation_1 # True

I have not been able to find what techniques were used to achieve this level of accuracy with related objects in annotate method.

Any ideas ?

Thank's

Update : Thank's you Sumithran for your answer.

But I don't want all_validated I want to manage all_validation_1 next to all_validation_2 for some check.

If I take example on your solution, it almost work with a little throwback that I don't understant :

test = Test.objects.annotate(
    number_of_test=Count("testversion", distinct=True)
).annotate(
    all_validation_1=Case(
        When(Q(testversion__validation_1=True), then=Value(True)),
        default=Value(False),
        output_field=BooleanField(),
    ),
    all_validation_2=Case(
        When(Q(testversion__validation_2=True), then=Value(True)),
        default=Value(False),
        output_field=BooleanField(),
    )
)

But for some Test objects there is some duplication :

test.filter(name='test_27')
>> <QuerySet [<Test: test_27>, <Test: test_27>]>
test.filter(name='test_27')[0] == test.filter(name='test_27')[1]
>> True
test.filter(name='test_27')[0].all_validation_1
>> True
test.filter(name='test_27')[1].all_validation_1
>> False

What I'm doing wrong ?

To avoid duplication use distinct() method in the queryset, I habe update the answer. — Sumithran, Oct 24 '22 at 13:37
Sorry forget to precise that .distinct() don't do the tricks on Sqlite. Maybe on other database it could work ? — Florian, Oct 24 '22 at 13:38
You can use .discinct() with SQLite, but if you want to use .distinct("some_filed_name") then you should use some other db like Postgre.. — Sumithran, Oct 24 '22 at 13:44
Yes sure, I tried .distinct() on SQLite but there are still duplicated objects, I don't know why. — Florian, Oct 24 '22 at 13:45
Sorry you cant use .distinct() in this scenario, you should use distinct("id") in order to solve this.. unfortunately this is not possible with SQLite, you have to switch the db, preferably PostgreSQL. — Sumithran, Oct 24 '22 at 13:48
Ok, so you think there is no other solution for these kind of requests ? As soon as I have tested on another database, I will accept your answer. — Florian, Oct 24 '22 at 13:51

Sumithran · Answer 1 · 2022-10-24T13:34:19.467

You can make use of Django's Conditional expressions in combination with the Q objects.

Give this a try

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

test = Test.objects.annotate(
    number_of_test=Count("testversion", distinct=True)
).annotate(
    all_validated=Case(
        When(
            Q(testversion__validation_1=True)
            & Q(testversion__validation_2=True)
            & Q(testversion__validation_3=True)
            & Q(testversion__validation_4=True),
            then=Value(True),
        ),
        default=Value(False),
        output_field=BooleanField(),
    )
).distinct()

if all of your test validations are True then the value of output filed all_validated will be True otherwise it be False

score 0 · Answer 2 · answered Oct 24 '22 at 15:19

I finally find an other answer :

from test.models import Test, TestVersion
from django.db.models import Count, Case, When, Exists, OuterRef, Value, BooleanField

test = Test.objects.filter(
    user=request.user
).annotate(
    number_of_test=Count("testversion", distinct=True),
    all_validation_1=Case(
        When(
            Exists(TestVersion.objects.filter(test=OuterRef('pk'), validation_1=False)), 
            then=Value(False)
        ),
        default=Value(True),
        output_field=BooleanField()
    )
).distinct()

In this case :

>> test.first().all_validation_1 == all(test.first().testversion_set.all().values_list('validation_1', flat=True))
True

So I just need to reiterate the same thing for validation_2, 3 and 4 and it should be ok.

But I think it will be a little bit fat code. Or it can be a good practice ?

Let me know before I check the answer.

How to Django queryset annotate True when all BooleanField of related objects are True else False?

2 Answers2