[Fixed]-Django ORM IF statement

by

1👍

To obtains the same SQL output, you may use the following queryset:

already_checked = Sum(Func('checked', function='IF', template='%(function)s(%(expressions)s=0, 0, 1)'))
not_checked = Sum(Func('checked', function='IF', template='%(function)s(%(expressions)s=0, 1, 0)'))
check_finished = Func(
        not_checked,
        function='IF', template='%(function)s(%(expressions)s=0, 1, 0)'
)
Refuels.objects.values('car_id').annotate(
    total_amount=Sum('amount'),
    already_checked=already_checked,
    not_checked=not_checked,
    check_finished=check_finished
)

Check the doc on expressions for more informations.

Now, already_checked could be simplified with:

already_checked = Sum('checked')

And instead of having the not_checked and check_finished annotations, you could annotate the count and easily compute them in Python, for example:

qs = Refuels.objects.values('car_id').annotate(
    count_for_car=Count('car_id'),
    total_amount=Sum('amount'),
    already_checked=Sum('checked'),
)

for entry in qs:
    not_checked = entry['count_for_car'] - entry['already_checked']
    check_finished = not_checked == 0
👤aumo

Leave a comment