Django reverse to contains/icontains
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)?
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.
output = MyModel.objects.filter(Q(name__contains="Mr. Peter Johnson"))
上一篇: 根据名称过滤django QuerySet的最佳方式（等级）
下一篇: Django反转为包含/图标