Django filter queryset on “tuples” of values for multiple columns

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

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)

Advertisement

Answer