13đź‘Ť
While extras do provide an expanded complex functionality for edge cases, extras should be treated as last resort and likely going to be deprecated at some point.
This can be accomplished using annotate and filtering.
from django.db.models import F, Value, CharField
MyUserModel.objects \
.annotate(my_name_field=Value('Mr. Peter Johnson', output_field=CharField())) \
.filter(my_name_field__icontains=F('name'))
Genericized:
from django.db.models import F, Value, CharField
@staticmethod
def reverse_case_insensitive_contains(model, search_field_name: str, search_field_value: str):
return model.objects \
.annotate(search_field=Value(search_field_value, output_field=CharField())) \
.filter(search_field__icontains=F(search_field_name))
3đź‘Ť
Unfortunately, Django’s ORM doesn’t have anything built-in for reverse LIKEs. But an .extra()
clause may make it a bit easier than a raw query.
I used something like this:
qs.extra(
where=['''%s LIKE %s.%s'''],
params=(
"string to match",
FooModel._meta.db_table,
"bar_field",
),
)
The problems with the code above are that
1) it doesn’t work with sqlite backend in this form (“syntax error near .”, it does work with table/column names hardcoded in query… which is not always safe and always ugly);
and 2) it requires FooModel.bar_field to have data %in like style%
so you can’t match arbitrary strings (this can be fixed with a query like %s LIKE CONCAT("%", %s.%s, "%")
but it’ll make it DBMS-specific, which is not good).
Reversed LIKE itself should probably work with any major DBMS, but I tested it only on sqlite and postgres.
Maybe someone should generalize my solution and create a reusable, DBMS-agnostic app with special subclassed queryset/manager/Q-object for this specific task…
- How to use ModelMultipleChoiceFilter?
- A good way to encrypt database fields?
- Django request.user.is_superuser doesn't work
- How do I serve media files in a local Django environment?
- Graphene-python performance issues for large data sets
3đź‘Ť
If you are on the latest version of Django (1.10 or later) and using Postgres the ORM can handle this. Check out the docs.
A trigram_similar
lookup will get you what you are looking for:
qs = MyModel.objects.filter(name__trigram_similar='Mr. Peter Johnson')
Don’t forget to enable this lookup by enabling the pg_tgrm extension. You can do that with a django migration.
And you will need to add 'django.contrib.postgres'
to your INSTALLED_APPS
setting.
- What does deconstructible do in Python?
- Remove non-ASCII characters from a string using python / django
- How to assert django uses particular template in pytest
- Model Method from rest_framework modelSerializer
- Create DB Constraint via Django