Django filter queryset on "tuples" of values for multiple columns

Question

Say I have a model:

Class Person(models.Model):     firstname = models.CharField()     lastname = models.CharField()     birthday = models.DateField()     # etc... 

and say I have a list of 2 first names: first_list = ['Bob', 'Rob'] And I have a list of 2 last names: last_list = ['Williams', 'Williamson']. Then if I wanted to select everyone whose first name was in first_list I could run:

Person.objects.filter(firstname__in=first_list) 

and if I wanted to select everyone whose last name was in last_list, I could do:

Person.objects.filter(lastname__in=last_list) 

So far, so good. If I want to run both of those restrictions at the same time, that's easy...

Person.objects.filter(firstname__in=first_list, lastname__in=last_list) 

If I wanted to do the or style search instead of the and style search, I can do that with Q objects:

Person.objects.filter(Q(firstname__in=first_list) | Q(lastname__in=last_name)) 

But what I have in mind is something a bit more subtle. What if I just want to return a queryset that returns specific combinations of first and last names? I.e. I want to return the Person objects for which (Person.firstname, Person.lastname) is in zip(first_names, last_names). I.e. I want to get back anyone named the Bob Williams or Rob Williamson (but not anyone named Bob Williamson or Rob Williams).

In my actual use case, first_list and last_list would both have ~100 elements.

At the moment, I need to solve this problem in a Django app. But I am also curious about the best way to handle this in a more general SQL context.

Thanks! (And please let me know if I can clarify anything.)

Answer 1

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) 

Answer 2

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.