Creating a queryset which represents a union of querysets

Question

Let's say I have the following models:

class House(models.Model):
    address = models.CharField(max_length=255)

class Person(models.Model):
    name = models.CharField(max_length=50)
    home = models.ForeignKey(House, null=True, related_name='tenants')

class Car(models.Model):
    make = models.CharField(max_length=50)
    owner = models.ForeignKey(Person)

Let's say I have a need (strange one, albeit) to get:

list of people who live in a house or are named 'John'
list of cars of the above people

I would like to have two functions:

get_tenants_or_johns(house)
get_cars_of_tenants_or_johns(house)

I could define them as:

from django.db.models.query_utils import Q

def get_cars_of_tenants_or_johns(house):
    is_john = Q(owner__in=Person.objects.filter(name='John'))
    is_tenant = Q(owner__in=house.tenants.all())
    return Car.filter(is_john | is_tenant)

def get_tenants_or_johns(house):
    johns = Person.objects.filter(name='John')
    tenants = house.tenants.all()
    return set(johns) | set(tenants)

The problem is that the logic is repeated in the above examples. If I could get get_tenants_or_johns(house) to return a queryset I could define get_cars_of_tenants_or_johns(house) as:

def get_cars_of_tenants_or_johns(house):
    return Car.objects.filter(owner__in=get_tenants_or_johns(house))

In order to do that, get_tenants_or_johns(house) would need to return a union of querysets, without turning them into other collections.

I cannot figure out how to implement get_tenants_or_johns(house) so that it would return a queryset containing a SQL UNION. Is there a way to do that? If not, is there an alternate way to achieve what I am trying to do?

Krystian Cybulski · Accepted Answer · 2013-01-09T15:18:43.557

11

The | operator on two querysets will return a new queryset representing a union.

The function will need to change to (got rid of set() wrappers):

def get_tenants_or_johns(house):
    johns = Person.objects.filter(name='John')
    tenants = house.tenants.all()
    return johns | tenants

and everything will work exactly like needed.

edited Jan 09 '13 at 15:18

answered Jan 09 '13 at 15:12

Krystian Cybulski

10,789
12
67
98

Dan Hoerst · Answer 2 · 2013-01-09T15:20:13.630

0

You mention users who live in a house, but have no mention of your User model.

I think you really need to take a long look at the structure of your application - there are probably much easier ways to accomplish your goal.

But to answer your question let's set up three helper functions. Since, as I mentioned above, you haven't outlined what you want to do with the User class - I've assumed that the house that will be passed to these functions is an address:

helpers.py

def get_johns(house):
    is_john = Person.objects.filter(name='John')
    return is_john

def get_cars_of_tenants(house):
    cars = Car.objects.filter(owner__home__address=house)
    return cars

def get_tenants(house):
    tenants = Person.objects.filter(home__address=house)
    return tenants

Now you could create a view for each of your combination queries:

views.py:

import helpers.py
from itertools import chain

def get_cars_of_tenants_or_johns(request, house):
    results = list(chain(get_cars_of_tenants(house), get_johns(house)))
    return render_to_response('cars_or_johns.html', {"results": results,})

def get_tenants_or_johns(request, house):
    results = list(chain(get_tenants(house), get_johns(house)))
    return render_to_response('tenants_or_johns.html', {"results": results,})

And this can go on for all of the various combinations. What is returned is results which is a list of all of the matches that you can iterate over.

edited Jan 09 '13 at 15:20

answered Jan 09 '13 at 05:12

Dan Hoerst

6,222
2
38
51

Thank you for your feedback. It was a late night question and I included many typos. I did not mean to refer to User objects, but rather Person objects. I have modified the question. – Krystian Cybulski Jan 09 '13 at 14:50
Unfortunately, your suggestion `tenants + johns` does not work, although I very much wish it had. It throws a `TypeError` `unsupported operand type(s) for +: 'QuerySet' and 'QuerySet'`. While my original question sounds silly, it is an analogous problem to one I am facing professionally (which is more complicated but requires similar functionality). I wish it had not been voted down as it is a valid problem. – Krystian Cybulski Jan 09 '13 at 14:54
I've edited my post to use itertools `chain` to combine the QuerySets. Please try that. For more insight as to why/how this works, I would suggest checking out the itertools docs and this post: http://stackoverflow.com/questions/431628/how-to-combine-2-or-more-querysets-in-a-django-view – Dan Hoerst Jan 09 '13 at 15:12
I've found the answer I was looking for. It is the `|` operator on querysets. it does exactly what I needed. It is important for me to keep the querysets returned by the above functions as querysets and not turn them into lists, sets, or other containers. This way, I can call `.delete()` and `.count()` on these querysets. – Krystian Cybulski Jan 09 '13 at 15:28

Creating a queryset which represents a union of querysets

2 Answers2