[SOLVED] Writing a tuple search with Django ORM

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.

Solution

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.