Submit Blog  RSS Feeds

Wednesday, June 13, 2012

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

2 comments:

  1. Thank you, you made my day!
    For information,

    PostgreSQL way is:
    MyModel.objects.extra(where=["%s LIKE '%%' || some_field || '%%'"], \
    params=["some phrase"])

    ReplyDelete
  2. You're welcome :) Thanks for PostgreSQL update!

    ReplyDelete

free counters