[Django]-Django filter queryset on "tuples" of values for multiple columns

by

38👍

✅

I don’t see much solutions except for a big OR clause:

import operator
from itertools import izip
query = reduce(
    operator.or_, 
    (Q(firstname=fn, lastname=ln) for fn, ln in izip(first_list, last_list))
    )

Person.objects.filter(query)
👤bruno desthuilliers

16👍

bruno’s answer works, but it feels dirty to me – both on the Python level and on the SQL level (a large concatenation of ORs). In MySQL at least, you can use the following SQL syntax:

SELECT id FROM table WHERE (first_name, last_name) IN
       (('John','Doe'),('Jane','Smith'),('Bill','Clinton'))

Django’s ORM doesn’t provide a direct way to do this, so I use raw SQL:

User.objects.raw('SELECT * FROM table WHERE (first_name, last_name) IN %s',
      [ (('John','Doe'),('Jane','Smith'),('Bill','Clinton')) ])

(This is a list with one element, matching the single %s in the query. The element is an iterable of tuples, so the %s will be converted to an SQL list of tuples).

Notes:

  1. As I said, this works for MySQL. I’m not sure what other backends support this syntax.
  2. A bug in python-mysql, related to this behavior, was fixed in November 2013 / MySQLdb 1.2.4, so make sure your Python MySQLdb libraries aren’t older than that.
👤Isolated Ostrich

0👍

Using python 3.5 version :

import operator
import functools

query = functools.reduce(
    operator.or_, 
    (Q(firstname=fn, lastname=ln) for fn, ln in zip(first_list, last_list))
    )

Person.objects.filter(query)
👤santhosh_dj

Leave a comment