Using django how can I combine two queries from separate models into one query?
In my specific case, I have two kinds of "messages" that I need to retrive and paginate.
Let's omit the details, and just say that the first kind is in a model called Msg1 and the other is called Msg2
The fields of these two models are completely different, the only fields that are common to the two models are "date" and "title" (and of course, id).
I can get Msg1.objects.all() and Msg2.objects.all() but can I combine these two queries into one query, sort it by date, and paginate it?
I need to preserve the lazy nature of the query.
The trivial solution is to list(query) both queries and combine them in a python list. but this is inefficient for obvious reasons.
I looked through the django references on models and dp-api, but it doesn't seem that there is a way to combine queries of different models/tables into one.
I would suggest that you use Model inheritance.
Create a base model that contains date and title. Subclass Msg1 and Msg2 off it as described. Do all your queries (to fill a page) using the base model and then switch to the derived type at the last moment.
The really great thing about inheritance is that django then allows you to use the base model in foreign keys from other models, so you can make your whole application more flexible. Under the hood it is just a table for the base model with a table per sub-model containing one-to-one keys.
"combine these two queries into one query, sort it by date, and paginate it?"
That's the SQL union. Leave the Django ORM and use a SQL union. It's not brilliantly fast because SQL has to create a temporary result, which it sorts.
Create the temporary result, which can be sorted. Since a list has a sort method, you'll have to merge the two results into one list.
Write a merge algorithm that accepts two query sets, paginating the results.
Edit. Here's a merge algorithm.
def merge( qs1, qs2 ): iqs1= iter(qs1) iqs2= iter(qs2) k1= iqs1.next() k2= iqs2.next() k1_data, k2_data = True, True while k1_data or k2_data: if not k2_data: yield k1 try: k1= iqs1.next() except StopIteration: k1_data= False elif not k1_data: yield k2 try: k2= iqs2.next() except StopIteration: k2_data= False elif k1.key <= k2.key: yield k1 try: k1= iqs1.next() except StopIteration: k1_data= False elif k2.key < k1.key: # or define __cmp__. yield k2 try: k2= iqs2.next() except StopIteration: k2_data= False else: raise Exception( "Wow..." )
You can fold in pagination:
def paginate( qs1, qs2, start=0, size=20 ): count= 0 for row in merge( qs1, qs2 ): if start <= count < start+size: yield row count += 1 if count == start+size: break