Django "is substring" like filter (MySQL)

Recently I came by a problem of selecting rows from a table on a condition that one of the fields is a substring of a given phrase. It's a bit hard to explain, but anyway I wanted to achieve the equivalence of:

filter(lambda x: "some phrase".find(x.some_field) >= 0, MyModel.objects.all())

on the SQL/ORM level.


So I searched the django documentation again and again and I failed to find anything useful. If the problem cannot be solved on the ORM level it must be solved with raw SQL:

SELECT * FROM myapp_mymodel WHERE "some phrase" LIKE CONCAT('%',some_field,'%');

Since the presented where clause connot be generated using the QuerySet.filter method we have to use the extra method instead. A django equivalent would look like this:

MyModel.objects.extra(where=["%s LIKE CONCAT('%%', some_field, '%%')"], \
    params=["some phrase"])


We should remember that the SQL LIKE operator is case insensitive (in this case it is desirable), however if you want a case sensitive filter, try using LIKE BINARY  instead.


~KR