Django reverse to contains/icontains

Question

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

Answer 1

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

Answer 2

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

Answer 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.