Getting a UNION query in Django Models?

[Michael Hipp]

How do I do something like this using Django Models?

SELECT name,birthdate FROM friends
UNION
SELECT name,birthdate FROM enemies
ORDER BY birthdate, name;

I can't find any reference in the Django docs to getting a UNION.

Thanks,
Michael

[Tim Chase]

> How do I do something like this using Django Models?
>
> SELECT name,birthdate FROM friends
> UNION
> SELECT name,birthdate FROM enemies
> ORDER BY birthdate, name;

several alternatives:

Use a custom manager:
####################################################
class FriendManager(Manager):
def __init__(self, is_friend):
self.is_friend = is_friend
def get_query_set(self):
return super(FriendManager, self).get_query_set(
).filter(is_friend=self.is_friend)

class People(Model):
name = CharField(...)
birthdate = DateField(...)
is_friend = BooleanField(...)
friends = FriendManager(True)
enemies = FriendManager(False)
objects = Manager()
...
friends = People.friends.all()
enemies = People.enemies.all()
everybody = People.objects.all()
young_friends = People.friends.filter(
birthdate__gt=date(1990,1,1))
old_enemies = People.enemies.filter(
birthdate__lt=date(1943,1,1))
####################################################

Or, you could just use itertools.chain:

for person in itertools.chain(
Friends.objects.all(),
Enemies.objects.all()):
print person.name, "(%s)" % person.birthdate

-tim

[Rajesh Dhawan]

Hi Michael,

The Django ORM essentially maps one DB table to one Django model
class. Since, in your example, friends and enemies are two different
tables i.e. two different Django model classes, the above query is not
possibly using the Django Model API. However, you can execute this
query as well as other complex queries using by dropping in to raw
SQL.

See this for an example:
http://www.djangoproject.com/documentation/model-api/#executing-custom-sql

-Rajesh Dhawan

[Michael Hipp]

Thanks. Unfortunately doing it that way returns a list of tuples - a bit
hard to work with.

Is there some way to get a "normal" (i.e. dictionary-like) response
without having to resort to making my own connection with psycopg2?

Thanks,
Michael

[Rajesh Dhawan]

On Feb 25, 3:48 pm, Michael Hipp <Mich...@Hipp.com> wrote:
> Rajesh Dhawan wrote:
> > Hi Michael,
>
> >> How do I do something like this using Django Models?
>
> >> SELECT name,birthdate FROM friends
> >> UNION
> >> SELECT name,birthdate FROM enemies
> >> ORDER BY birthdate, name;
>
> >> I can't find any reference in the Django docs to getting a UNION.
>
> > The Django ORM essentially maps one DB table to one Django model
> > class. Since, in your example, friends and enemies are two different
> > tables i.e. two different Django model classes, the above query is not
> > possibly using the Django Model API. However, you can execute this
> > query as well as other complex queries using by dropping in to raw
> > SQL.
>
> > See this for an example:

> >http://www.djangoproject.com/documentation/model-api/#executing-custo...

>
> Thanks. Unfortunately doing it that way returns a list of tuples - a bit
> hard to work with.
>
> Is there some way to get a "normal" (i.e. dictionary-like) response
> without having to resort to making my own connection with psycopg2?

Firstly, the aforementioned custom SQL method doesn't use a raw
psycopg2 connection. It uses a Django connection object (exactly what
Django's own Model API queries use).

And, here's a way to make your result rows return a dictionary rather
than a tuple using standard Python constructs (dict() & zip()):

from django.db import connection
cursor = connection.cursor()
column_names = ('name', 'birthdate')
cursor.execute('''SELECT name, birthdate FROM friends
UNION
SELECT name, birthdate FROM enemies
ORDER BY birthdate, name''')
while True:
row = cursor.fetchone()
if row is None: break
mapped_row = dict(zip(column_names, row))
print mapped_row