Django custom lookup, rhs receives %s only

I want to create a custom icontainsall lookup such that when a user searches for a phrase with a space in it, I want to convert it into a like on all words with AND (so that order of word does not matter). i.e. If user searches for 'egg milk', the predicate becomes name LIKE %egg% AND name LIKE %milk%.

This is how my lookup looks:

from django.db.models import CharField, Lookup, TextField


class CaseInsensitiveContainsAllLookup(Lookup):
    lookup_name = 'icontainsall'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        print(lhs, rhs, lhs_params, rhs_params)
        # shows "mytable"."name" %s [] ['egg milk']
        predicate, params = [], []
        # escape \ and %
        rhs_list = rhs.replace('\\', '\\\\').replace('%', '\\%').split(' ')
        for rhs in rhs_list:
            params += lhs_params + rhs_params
            predicate.append(f"LOWER({lhs}) LIKE LOWER({rhs})")
        return ' AND '.join(predicate), params


CharField.register_lookup(CaseInsensitiveContainsAllLookup)
TextField.register_lookup(CaseInsensitiveContainsAllLookup)

However, rhs receives only %s and not egg milk. How can I go about achieving what I want using a lookup?

Edit: I displayed lhs_params and rhs_params. Trying to think why are those a list. If and when I understand, I think I can solve my own problem.

The process_lhs and process_rhs methods return a tuple with the first element being a SQL expression string and the second being the list of parameters for the SQL expression. Considering this rhs being %s makes sense since that is the default implementation of Lookup.process_rhs. The value you require is present in rhs_params.

Also rather than trying to implement the escaping and case insensitive comparision logic on your own you should let Django do the heavy lifting by utilizing the IContains builtin lookup:

from django.db.models.lookups import IContains


class CaseInsensitiveContainsAllLookup(Lookup):
    lookup_name = 'icontainsall'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        
        flattened_rhs_params = []
        for param in rhs_params:
            flattened_rhs_params += param.split(' ')
        
        predicate, params = [], []
        for rhs_param in flattened_rhs_params:
            contains_sql, contains_params = IContains(self.lhs, rhs_param).as_sql(compiler, connection)
            params += contains_params
            predicate.append(contains_sql)
        return ' AND '.join(predicate), params

Note: if your input has multiple spaces between words with the current splitting implementation you'll end up having emtpy strings in the flattened_rhs_params depending on your use case you might want to filter those empty strings out.

Back to Top