[Django] #34029: Use case that require QuerySet.extra

[Django]

#34029: Use case that require QuerySet.extra
-------------------------------------+-------------------------------------
Reporter: fatah4561 | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: 4.0
layer (models, ORM) |
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/34029>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#34029: Use case that require QuerySet.extra
-------------------------------------+-------------------------------------

Reporter: Fath | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => invalid

Comment:

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/34029#comment:1>