[Django] #34942: Add icontains support for PostgreSQL ArrayField

9 views

Skip to first unread message

Django

unread,

Nov 2, 2023, 2:28:12 PM11/2/23

to django-...@googlegroups.com

#34942: Add icontains support for PostgreSQL ArrayField
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
aminabbasov |
Type: New | Status: new
feature |
Component: Database | Version: 4.2
layer (models, ORM) | Keywords: QuerySet.extra,
Severity: Normal | ArrayField, PostgreSQL, icontains
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Django ORM for PostgreSQL ArrayField allows to make only "contains", and
doesn't support "icontains", although the database itself supports this
type of query.

For example, I have this Product table:

{{{#!python
class Product(models.Model):
name = models.CharField(max_length=255)
options = ArrayField(base_field=models.CharField(max_length=255))

def __str__(self):
return self.name
}}}

And only two rows in table:

||= id =||= name =||= options =||
|| 1 || First || ["foo", "Bar", "BAZ"] ||
|| 2 || Second || ["Foo", "Bar", "baZ"] ||

If I want to filter rows by options сolumn, I can make only this ORM
queries:

{{{#!bash
>>> Product.objects.filter(options__contains: "foo")
<ProductQuerySet [<Product: First>]>

>>> Product.objects.filter(options__contains: "Bar")
<ProductQuerySet [<Product: First>, <Product: Second>]>
}}}

But if I want to do case-insensitive filtering,
{{{Product.objects.filter(options__icontains: "foo")}}} wouldn't work. So,
the only option to do this query, is to use {{{.extra()}}} method:

{{{#!bash
>>> Product.objects.extra(
... where=['%s ILIKE ANY (options)'],
... params=["foo"],
... )
<ProductQuerySet [<Product: First>, <Product: Second>]>
}}}

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

Django

unread,

Nov 3, 2023, 5:06:21 AM11/3/23

to django-...@googlegroups.com

#34942: Add icontains support for PostgreSQL ArrayField
-------------------------------------+-------------------------------------

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

Changes (by David Sanders):

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

Comment:

Hi aminabbasov,

Note you can write your own lookups easily with Django, there's a tutorial
on how to do that: https://docs.djangoproject.com/en/4.2/howto/custom-
lookups/

Following that guide and altering slightly so that it registers on
`ArrayField` only and swapping the rhs & lhs so it takes on the format
`arg ILIKE ANY(field)`, then your `icontains` array lookup would be
something like this:

{{{
@ArrayField.register_lookup
class ArrayIContains(Lookup):
lookup_name = "icontains"

def as_sql(self, compiler, connection):
lhs, lhs_params = self.process_lhs(compiler, connection)
rhs, rhs_params = self.process_rhs(compiler, connection)
params = rhs_params + lhs_params
return "%s ILIKE ANY(%s)" % (rhs, lhs), params

}}}

As for whether this should be part of Django: The normal process is to
bring this up on the forum and get folks to vote on its inclusion, though
consensus usually is that we don't want to include every possible kitchen
sink feature in the codebase when it's easy enough to write your own.
Please see https://code.djangoproject.com/wiki/DevelopersMailingList for
more details.