1

As the title mentions, I am working in Django and trying to make a QuerySet to return all "Customer" models that have a name value that is a substring of my query_string.

I want something like this:

Customer.objects.filter(firstName__icontains=query_string)

But in reverse:

Customer.objects.filter(query_string__icontains=firstName)

Except that obviously doesn't work.

I am struggling to find any information on how I would go about this.

Thanks in advance.

Edit My expected input will be in Japanese - Kana and Kanji　かな、カナ、漢字

Kurisubo
  • 311
  • 2
  • 8
  • https://stackoverflow.com/questions/37781832/django-how-to-use-filter-to-check-if-string-field-is-contained-in-parameter – Đào Minh Hạt Mar 06 '19 at 05:10
  • I thought about doing something similar to that, but unfortunately the site I am making is in Japanese. Japanese names are usually searched without any spaces between the names, making that solution unusable for me. :/ Unless of course I split the search bar into two separate inputs - but I am trying to avoid that if possible. – Kurisubo Mar 06 '19 at 05:20
  • use the second answer not the selected one :D – Đào Minh Hạt Mar 06 '19 at 06:41
  • I attempted the second one as well. I am getting the same error I got in Brad 's answer "Cannot resolve keyword query_string into field." – Kurisubo Mar 06 '19 at 08:08

2 Answers2

3

You can define a custom lookup. This one below is Postgres specific:

class LowerContainedBy(Lookup):
    lookup_name = 'icontained_by'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return f"LOWER({rhs}) LIKE '%%' || LOWER({lhs}) || '%%'", params

CharField.register_lookup(LowerContainedBy)

Then you can do:

Customer.objects.filter(firstName__icontained_by=query_string)
Brad Martsberger
  • 1,747
  • 13
  • 7
  • Struggling to get this one to work.. when I put "query_string" on the left hand side I get a "Cannot resolve keyword" error. – Kurisubo Mar 06 '19 at 07:44
  • Sorry, my copy_and_paste error, I'll edit to be correct – Brad Martsberger Mar 06 '19 at 12:33
  • I think your answer is the closest! I came back to this problem and tried to edit your code for MySQL. Instead of return f"LOWER({rhs}) LIKE '%%' || LOWER({lhs}) || '%%'", params I now have return "LOWER(%s) LIKE LOWER(%s)" % (rhs, lhs), params This works if I type a name that is identical to the one in the table. I need to add wildcard characters, but I keep getting errors every time I add them to this MySQL query. – Kurisubo Mar 20 '19 at 08:50
  • I was able to figure the problem! See the answer below. Thank you so much for your help! – Kurisubo Mar 22 '19 at 02:40
0

Thanks to everyone for the answers! I was able to solve my problem by building off of @brad's answer.

I took his approach of defining a custom lookup. It pretty much looks like his, but I configured it for MySQL.

class LowerContainedBy(Lookup):
lookup_name = 'icontained_by'

def as_mysql(self, compiler, connection):
    lhs, lhs_params = self.process_lhs(compiler, connection)
    rhs, rhs_params = self.process_rhs(compiler, connection)
    params = lhs_params + rhs_params
    return  "LOWER({0}) LIKE LOWER(CONCAT('%%',{1},'%%'))".format(lhs, rhs), params

Field.register_lookup(LowerContainedBy)

(I also noticed the rhs and lhs was flipped in his answer so keep that in mind!)

And finally by implementing the new lookup like so:

searchResults = Customer.objects.filter(firstName__icontained_by=search_string).exclude(contract='').order_by('-dateCreated')
Kurisubo
  • 311
  • 2
  • 8