In this question was solved problem for reverse LIKE operation in SQL, for example if field name is "Peter Johnson", we could find it by such query:
select name from user where "Mr. Peter Johnson" like CONCAT('%', name, '%')
Is there any way to do such thing in Django Q object (I'm building a big query, so using raw SQL query will not be rational)?
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))
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...
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.
