Opened 10 months ago
Closed 10 months ago
#34942 closed New feature (wontfix)
Add icontains support for PostgreSQL ArrayField
Reported by: | aminabbasov | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.2 |
Severity: | Normal | Keywords: | QuerySet.extra, ArrayField, PostgreSQL, icontains |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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>]>
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 formatarg ILIKE ANY(field)
, then youricontains
array lookup would be something like this: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!