Django queryset icontains filter but "vise versa"

Question

I have a Model:

class Sources(models.Model):
    ...
    vendor = models.CharField(max_value=128, blank=True, null=True)
    ...

And for example some data:

select id, vendor from "webgui_sources";
 id |       vendor
----+-----------------
  6 | oracle
  5 | microsoft

Then I have a string like "oracle;microsoft;apache" and I need to check are there models where vendor value is included to this string. So basically I need to collect all vendors which vendor value is included to "oracle;microsoft;apache".

Whereas

Sources.objects.filter(vendor__icontains='oracle;microsoft;apache')

produces the following SQL-query:

SELECT * FROM "webgui_sources" WHERE UPPER("webgui_sources".vendor) LIKE UPPER('oracle;microsoft;apache')

I need to swap the fields and get something like this:

SELECT * FROM "webgui_sources" WHERE UPPER('oracle;microsoft;apache') LIKE UPPER("webgui_sources".vendor)

I tried to use extra and it did the job, but since using extra is not advised I tried to create custom lookup:

class IContainsReverse(PatternLookup):
    
     lookup_name = "icontains_reverse"

    

     def as_sql(self, compiler, connection):
        
          lhs_sql, params = self.process_lhs(compiler, connection)
        
          params.extend([self.rhs])
        
          return "%s ~* {}".format(lhs_sql), params


 

Field.register_lookup(IContainsReverse)

This lookup allows me to produce a query like this:

Sources.objects.filter(vendor__icontains_reverse='oracle;microsoft;apache')

select * from "webgui_sources" where 'oracle;microsoft;apache' ~* "webgui_sources".vendor;

More or less it works but it looks clunky and works only with PostgreSQL. I hope that there is a more Django-way solution to do that, could somebody advice any more Django-way alternatives? Thanks in advance.

Answer 1

You can inject the value with an .alias(…) ^[Django-doc] for the expression, and then .filter(…) ^[Django-doc] on that alias:

from django.db.models import F, Value

Sources.objects.alias(filter_value=Value('oracle;microsoft;apache')).filter(
    filter_value_icontains=F('vendor')
)

that being said, you can make a query that works with:

from django.db.models import Q

vals = 'oracle;microsoft;apache'
Sources.objects.filter(
    *[Q(vendor__iexact=v) for v in vals.split(';')], _connector=Q.OR
)