Use case that require QuerySet.extra

[Fath]

so um i read on the documentation that this API will be removed but i need it for special use case so let's say i have an app that process coupons data or something similiar, that has a model with field "is_active" as boolean field (0, 1), and i also have 2 date field "expired_date", "start_date" the date field is self explanatory start date define when is the coupon can start being used while the expire define when the coupon no longer be able to used. Meanwhile the is_active define if the coupon is active or not.

So where do i need to use extra(), well the thing is i have a dynamic field that use WHEN CASE in sql (in postgresql, while the mysql equivalent might be the IF ELSE function), the field is called "coupon_status" with the condition:

1. when the is_active == 0 then status is "not activated"
2. when the is_active == 1 and the "start_date" > date.now() then status is "not yet activated"
3. when the is_active == 1 and the "start_date" <= date.now() and the "expired_date" >= date.now() then status is "active"
4. when the is_active == 1 and the "expired_date" < date.now() then status is "expired"

so the queryset look something like this:

Coupon.objects.extra(
                select={
                    'coupon_status':"SELECT 
CASE 
WHEN (coupon.is_active = '0') THEN 'not activated' 
WHEN (coupon.is_active = '1') AND ( coupon.start_date > CURRENT_DATE ) THEN 'not yet activated' 
WHEN (coupon.is_active = '1') AND ( coupon.start_date <= CURRENT_DATE ) AND ( coupon.expired_date >= CURRENT_DATE ) THEN 'active' 
WHEN (coupon.is_active = '1') AND ( coupon.expired_date < CURRENT_DATE ) THEN 'expired' END",
                }
            ).all()

It would be great if django will be able to provide support for dynamic field like this, because the way QuerySet.extra() now i can't use the filter() function from the extra field, but able to sort it with order() method.

[Mariusz Felisiak]

You can use the Case() expression.

If you're having trouble understanding how Django works, see TicketClosingReasons/UseSupportChannels for ways to get help. Trac is not a support channel.