#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>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018fca8bcef9-30332a0d-c226-4a51-84e5-f7682cf4983a-000000%40eu-central-1.amazonses.com.
