[Answered ]-Django query WHERE 1 IN (column1, column2)

2👍

Try using Q and just check against each column

from django.db.models import Q

MyModel.objects.filter(Q(column1=1) | Q(column2=2))

Which will case sql something like

SELECT * FROM sometable WHERE column1=1 OR column2=1

0👍

I ended up implementing a custom filter to make this “reverse IN”, so that you can query the columns instead of the values.

The filter works like this

col_name_1__revin=[value, 'col_name_2', 'col_name_3']

and it generates

value IN ("table_name"."col_name_1", "table_name"."col_name_2", "table_name"."col_name_3")

Only tested on IntegerField but should work with the other fields too, as long as you provide the correct value type.

from django.db.models import lookups
import re

class ReverseIn(lookups.In):
    lookup_name = 'revin'

    def get_prep_lookup(self):
        # We will always call this on a ChampionIntegerField with lookup_name = revin
        if not hasattr(self.rhs, '__iter__'):
            raise ValueError('ReverseIn only works with iterables. Got {} of type {}.'.format(self.rhs),type(self.rhs))
        self.table_name = self.lhs.field.model._meta.db_table
        self.output_field = self.lhs.output_field
        rhs = list(self.rhs)
        lhs = self.lhs.field.column
        try:
            self.lhs = self.lhs.output_field.get_prep_lookup("exact", rhs[0])
        except (TypeError, ValueError) as e:
            raise ValueError('The type of the first item of the argument must be compatible with the type of the column', e)
        return [lhs] + rhs[1:]

    def process_rhs(self, compiler, connection):
        if self.rhs_is_direct_value():
            # Do the quoting yourself, as Django outputs ' instead of " for parameters, and PostgreSQL complains
            # THIS IS DANGEROUS!!!!! I wish I could do it with parameters...
            def sql_sanitize(value):
                # Check if the value respects the rules for column names
                if re.match(r'^[a-zA-Z_][a-zA-Z0-9_]*', value):
                    return value
                else:
                    raise ValueError("The argument {} is not a valid column name".format(value))

            sql = ", ".join('"{}"."{}"'.format(self.table_name, sql_sanitize(value)) for value in set(self.rhs))
            params = []
            return sql, params
        else:
            raise NotImplementedError("ReverseIn only supports direct values")

    def as_sql(self, compiler, connection):
        rhs, rhs_params = self.process_rhs(compiler, connection)
        sql = "%s IN ({rhs})".format(rhs=rhs)
        params = [self.lhs]
        params.extend(rhs_params)
        return sql, params

IntegerField.register_lookup(ReverseIn)

Leave a comment