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

Question

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?

Answer 1

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.