# Three-table queryset (full outer join?) in Django

I've got three tables I need query to display to a user for a web application I've written. Each table (Url, Note, Quote) has a foreign key relation to the User table. For every User, I need to sort all Bookmarks, Notes and Quotes based on a _date_created_ field, then deliver that to the template as one iterable. This is for a toy/self-learning project so I don't really need to worry about scale.

The approach I've taken is this:

from operator import attrgetter

from app.models import Url, Note, Quote

date_dict = {}

for url in Url.objects.filter(user=request.user):
date_dict[url.date_created] = Url.objects.get(date_created=url.date_created)

for note in Note.objects.filter(user=request.user):
date_dict[note.date_created] = Note.objects.get(date_created=note.date_created)

for quote in Quote.objects.filter(user=request.user):
date_dict[quote.date_created] = Quote.objects.get(date_created=quote.date_created)

my_query = sorted((date_dict[i] for i in date_dict.iterkeys()),
key=attrgetter("date_created"),
reverse=True)


I have also tried this:

from operator import attrgetter
from itertools import chain

from app.models import Url, Note, Quote

items = sorted(chain(Url.objects.filter(user=request.user),
Note.objects.filter(user=request.user),
Quote.objects.filter(user=request.user)),
key=attrgetter("date_created"),
reverse=True)


I'm concerned how expensive this would get if I had huge data sets (I don't), but from what I've read the second is faster.

This code works and does what I expect it to, but is there a better/faster approach?

• You state that you want a sorted version, but nothing in your code sorts it. You also mention users being involved, but there are no users in your example code. – Winston Ewert Mar 8 '11 at 5:32
• @Winston Ewert: I've revised my code to better reflect my question. – mattdeboard Mar 8 '11 at 5:53
• Your current version will produce a bunch of None entries in the list. Also, it'll only count item for any particular date. I assume thats not what you want? – Winston Ewert Mar 8 '11 at 5:59
• @Winston Ewert: Hm, well, I've revised it to actually reflect what I've been doing in iPython, and I'm not getting any None-type responses. How would it produce a value of None? – mattdeboard Mar 8 '11 at 6:07
• the current version won't. A previous version looked like it would. – Winston Ewert Mar 8 '11 at 6:08

EDIT Removed previous version which was pretty much telling you to use the second method shown above. Your first method loads everything from the database twice. (Django may do some caching, but still) I'm not sure why you are putting everything in the dictionary.

The problem with the current implementation is that sorting is done by python rather then in the database. It would be better to have the sorting done in the database. There are two ways I see of doing that.

1. You could have the three different sources sorted by the database. Then you could perform a merge pass to combine them. However, you are probably going to have need a lot of data before that becomes a worthwhile strategy. Sorting will be implemently in C for python which will make it have a speed advantage over a merge which you would have to implement in python.

2. You could redesign your database so Urls, Quotes, and Notes are stored in the same table. That way you could request the database to sort them.

Both of these strategies are really only helpful if you end up with a lot of data. If you have that much data you aren't going to want to present it all to the user anyways. The result is that its probably not useful.

• Winston, thanks. #2 is what I'll be going for. I was using an abstract model for all the objects (which in Django is just a code reuse tool), but I'll be migrating over to a model somewhat like you described here. – mattdeboard Mar 8 '11 at 16:39