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

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 ?

CodePudding user response：

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

CodePudding user response：

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.