Add icontains support for PostgreSQL ArrayField

[aminabbasov]

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:

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:

>>> 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:

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

[David Sanders]

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.

Thanks!