CODEWITHSUNDEEP
pythondjangodjango-queryset
Bob Morane
Bob Morane

Reputation: 383

Writing a tuple search with Django ORM

I'm trying to write a search based on tuples with the Django ORM syntax.

The final sql statement should look something like:

SELECT * FROM mytable WHERE (field_a,field_b) IN ((1,2),(3,4));

I know I can achieve this in django using the extra keyword:

MyModel.objects.extra(
    where=["(field_a, field_b) IN %s"],
    params=[((1,2),(3,4))]
)

but the "extra" keyword will be deprecated at some point in django so I'd like a pure ORM/django solution.

Searching the web, I found https://code.djangoproject.com/ticket/33015 and the comment from Simon Charette, something like the snippet below could be OK, but I can't get it to work.

from django.db.models import Func, lookups

class ExpressionTuple(Func):
    template = '(%(expressions)s)'
    arg_joiner = ","


MyModel.objects.filter(lookups.In(
    ExpressionTuple('field_a', 'field_b'),
    ((1,2),(3,4)),
))

I'm using Django 3.2 but I don't expect Django 4.x to do a big difference here. My db backend is posgresql in case it matters.

Upvotes: 5

Views: 767

Answers (4)

zeta
zeta

Reputation: 39

I've created this class helpers to build the tuple IN clause

class TupleRow(models.Func):
    function = ""
    output_field: models.Field = models.CharField()


class TupleExpression(models.Expression):
    def __init__(
        self,
        values: list[tuple[models.Value, ...]],
        output_field: models.Field | None = None,
        *args: Any,
        **kwargs: Any,
    ) -> None:
        super().__init__(output_field=output_field, *args, **kwargs)
        self.values = values
        if output_field is None:
            self.output_field = models.CharField()

    def resolve_expression(
        self,
        query: Any,
        allow_joins: bool = True,
        reuse: bool | None = None,
        summarize: bool = False,
        for_save: bool = False,
    ) -> Any:
        # Resolve each value in the tuples
        resolved_values = [
            tuple(v.resolve_expression(query, allow_joins, reuse, summarize, for_save) for v in value)
            for value in self.values
        ]
        self.values = resolved_values
        return self

    def as_sql(self, compiler: Any, connection: Any) -> tuple[str, list[Any]]:
        # Convert the list of tuples into a SQL-compatible format
        sql_values = []
        params = []
        for value in self.values:
            sql_value = []
            for v in value:
                v_sql, v_params = compiler.compile(v)
                sql_value.append(v_sql)
                params.extend(v_params)
            sql_values.append(f"({', '.join(sql_value)})")
        sql = f"({', '.join(sql_values)})"
        return sql, params

You can use it as the output_field will help to parse correctly the type on SQL query

qs = MyModel.objects.alias(a=TupleRow("field_a", "field_b")).filter(
    a__in=TupleExpression(
        [
            (models.Value(1, output_filed=models.IntegerField()), models.Value(2, output_filed=models.IntegerField())),
            (models.Value(3, output_filed=models.IntegerField()), models.Value(4, output_filed=models.IntegerField())),
        ]
    )
)

Upvotes: 0

Bob Morane
Bob Morane

Reputation: 383

For reference and inspired from akshay-jain proposal, I managed to write something that works:

from django.db.models import Func,Value

def ValueTuple(items):
    return tuple(Value(i) for i in items)

class Tuple(Func):
    function = ''

qs = (
    MyModel.objects
    .alias(a=Tuple('field_a', 'field_b'))
    .filter(a__in=ValueTuple([(1, 2), (3, 4)])
)

It does produces a sql query like

SELECT * FROM table WHERE (field_a,field_b) IN ((1,2),(3,4));

And can be extended to more fields than just two.

I didn't do any benchmarks to compare it to Q objects filtering though.

Upvotes: 4

Akshay Jain
Akshay Jain

Reputation: 790

from django.db.models import Func, lookups

class Tuple(Func):
    function = '(%s)'

    def as_sql(self, compiler, connection):
        sql, params = super().as_sql(compiler, connection)
        if sql.endswith(',)'):
            sql = sql[:-2] + ')'
        return sql, params

MyModel.objects.filter((Func('field_a', function='(%s)'), Func('field_b', function='(%s)'))__in=[(1,2),(3,4)])

With these changes, the resulting SQL query should be something like:

SELECT * FROM mytable WHERE (field_a, field_b) IN ((1, 2), (3, 4))

Upvotes: 0

ruddra
ruddra

Reputation: 52018

I can think of one solution which will build the query beforehand using Q and then pass it through filter function:

q = Q()
for (item1, item2) in [(1,2),(3,4)]:
    q |= Q(field_one=item1, field_two=item2)

Mymodel.objects.filter(q)

Another more robust solution would be like this:

q = Q()

fields = ['field_one', 'field_two']

for item in [(1,2),(3,4)]:
   q |= Q(**dict(zip(fields, item)))

Here I am zipping the fields and item from the list of items, then passing it as an unpacked dictionary to Q. Its a similar implementation to previous example but here the number of fields can be many but it won't increase the number of lines in the code.

Upvotes: 5

Related Questions