Django: how to add compare condition in annotate queryset

Question

I want to add compare operations in annotate queryset to calculate value for specific field. How can I do that? This is my annotate queryset

sale_item_list = OrderItem.objects.filter(order_id=order.id) \
                .values('item__name') \
                .annotate(price=F('price')) \
                .annotate(exchange_rate=F('exchange_rate')) \
                .annotate(quantity=F('quantity') \
.annotate(amount=Case(
                        When(F('quantity') < F('inventory'), then=Sum(F('quantity') * F('price'))),
                        When(F('quantity') > F('inventory'), then=Sum(F('inventory') * F('price'))),
                        output_field=IntegerField()))

Conditional Expressions of my queryset above run error. Please help me fix it?

Answer 1

Try using the lesser and greater than field lookups __gt and __lt:

When(quantity__lt=inventory, then=Sum(F('quantity') * F('price'))),
When(quantity__gt=inventory, then=Sum(F('inventory') * F('price'))),

See https://docs.djangoproject.com/el/1.10/ref/models/querysets/#gt

Answer 2

Here is the solution for Django > 1.8 Adding annotation for two fields compare on equality.

queryset.annotate(
    is_custom=models.ExpressionWrapper(
        models.Q(field1__exact=models.F("field2")),
        output_field=models.BooleanField(),
    )
)

SQL equivalent

SELECT field1 = field2 AS is_custom, ...