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.
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.
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))
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.