id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34029	Use case that require QuerySet.extra	Fath	nobody	"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."	New feature	closed	Database layer (models, ORM)	4.0	Normal	invalid	QuerySet.extra		Unreviewed	0	0	0	0	0	0