Django reverse to contains / icons

Question

This problem was solved by the problem for the reverse LIKE operation in SQL, for example, if the field name is "Peter Johnson", we could find it by such a query

 select name from user where "Mr. Peter Johnson" like CONCAT('%', name, '%') 

Is there a way to do such a thing in a Django Q object (I am building a large query, so using a raw SQL query will not be rational)?

Answer 1

Unfortunately, Django ORM does not have a built-in for reverse LIKE. But a .extra() clause can make this a little easier than a raw request.

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 does not work with the sqlite backend in this form ("syntax error nearby"), it works with table / column names that are hard-coded in the query ... which are not always safe and always ugly);

and 2) FooModel.bar_field requires %in like style% data, so you cannot match arbitrary strings (this can be fixed with a query like %s LIKE CONCAT("%", %s.%s, "%") , but this will make it specific to the DBMS, which is not good).

Reverse LIKE itself should probably work with any large DBMS, but I tested it only on sqlite and postgres.

Perhaps someone should generalize my solution and create a multi-tasking DBMS-agnostic application with a special subclass of queryset / manager / Q-object for this specific task ...

Answer 2

If you use the latest version of Django (1.10 or later) and use Postgres, ORM can handle this. Check documents .

A trigram_similar lookup will bring you what you are looking for:

 qs = MyModel.objects.filter(name__trigram_similar='Mr. Peter Johnson') 

Remember to enable this search by including the pg_tgrm extension. You can do this with django migration .

And you need to add 'django.contrib.postgres' to the INSTALLED_APPS setting.

Answer 3

While add-ons provide advanced sophisticated functionality for extreme cases, add-ons should be considered a last resort and will probably be deprecated at some point.

This can be achieved by annotating 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')) 

generalized:

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

Answer 4

 output = MyModel.objects.filter(Q(name__contains="Mr. Peter Johnson"))