CODEWITHSUNDEEP
pythonsqldjangodjango-queryset
8one6
8one6

Reputation: 13778

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

Upvotes: 35

Views: 14177

Answers (4)

bruno desthuilliers
bruno desthuilliers

Reputation: 77912

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

import operator
from functools import reduce
from itertools import izip


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

Person.objects.filter(query)

Upvotes: 40

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477210

You can make a complex Q object that will look for combinations:

from django.db.models import Q

my_list = zip(first_names, last_names)

Person.objects.filter(
    *[
        Q(first_name=first_name, last_name=last_name)
        for first_name, last_name in my_list
    ],
    _connector=Q.OR
)

Upvotes: 1

santhosh_dj
santhosh_dj

Reputation: 455

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)

Upvotes: 0

Isolated Ostrich
Isolated Ostrich

Reputation: 1698

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.

Upvotes: 16

Related Questions