[Django] #35491: QuerySet.extra use case. Unnest array and make it lower. and make it an array again.

[Django]

#35491: QuerySet.extra use case. Unnest array and make it lower. and make it an
array again.
-------------------------------------+-------------------------------------
Reporter: june | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: 4.2
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 |
-------------------------------------+-------------------------------------
I had used extra() for annotating Arrayfield with lower cases and filter
out with another array intersection.

Below is what I used for

```
lower_value = [v.lower() for v in value]
numbers = Numeric.objects.extra(
where=[f"ARRAY(SELECT lower(unnest({array_field}))) &&
%s::text[]"],
params=[lower_value],
)
```

I need to unnest arrayfield first and make each elements lowercase, and
make it an Array again.
and then need to check overlay with another array.
--
Ticket URL: <https://code.djangoproject.com/ticket/35491>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35491: QuerySet.extra use case. Unnest array and make it lower. and make it an
array again.
-------------------------------------+-------------------------------------
Reporter: june | Owner: nobody

Type: New feature | Status: closed
Component: Database layer | Version: 4.2
(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 Simon Charette):

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

Comment:

There are multiple ways to achieve that without the usage of `extra`.

The most straightforward is to use `RawSQL`
[https://docs.djangoproject.com/en/4.2/ref/models/querysets/#extra as
mentioned in the docs] right below the part that mentions using creating a
ticket.

{{{#!python
Numeric.objects.filter(
RawSQL(

f"ARRAY(SELECT lower(unnest({array_field}))) && %s::text[]",

(lower_values,)
output_field=BooleanField(),
)
)
}}}

But you could also use a `Func`

{{{#!python
class ArrayLower(Func):
template = "ARRAY(SELECT lower(unnest(%(expressions)s)))"
arity = 1
output_field = ArrayField(TextField())

Numeric.objects.alias(
array_field_lower=ArrayLower("array_field")
).filter(
array_field_lower__overlap=lower_values
)
}}}

It would also be pretty straightforward to define an `__ioverlap`
[https://docs.djangoproject.com/en/4.2/howto/custom-lookups/#a
-transformer-example transform] if you wanted to to encapsulate all of
that logic

{{{#!python
Numeric.objects.filter(
array_field__ioverlap=value
)
}}}

In short, you don't need `extra` at all to achieve what you're after.
Please refer to
[https://docs.djangoproject.com/en/4.2/ref/models/expressions/ the
expression documentation] and familiarize yourself with `RawSQL` usage if
you want to resort to raw sql usage.
--
Ticket URL: <https://code.djangoproject.com/ticket/35491#comment:1>