[FIXED] How can I join multiple models in Django into a virtual table?

Issue

If I have 3 models, like:

class Cow(models.Model):
    name =
    number_of_eyes =
    number_of_feet =
    color =

class Pig(models.Model):
    name =
    number_of_eyes = 
    number_of_feet =
    intelligence = 
    
class Horse(models.Model):
    name =
    number_of_eyes =
    number_of_hooves = 
    weight_capacity =
    speed =

And I'm interested in making a single Livestock table in my template that has instances of all 3, but I'm only interested in these columns that all 3 models have:

• name
• number_of_eyes
• number_of_feet (number_of_hooves if Horse)

And we can ignore all other columns.

How can I join them into a single queryset?

The end goal is to get a single virtual table (queryset) that I can do a few other operations on (filter, order_by, slice), and then return the data in just those columns.

Is this possible in the Django ORM?

Solution

Apparently this can also be done using a Union, as suggested by Nick ODell:

from django.db.models import F

Cow.objects.filter(...).union(
  Pig.objects.filter(...), 
  Horse.objects.filter(...).annotate(number_of_feet=F("number_of_hooves"))
).values('name', 'number_of_eyes', 'number_of_feet').order_by('name')[:3]

Unfortunately you can't filter on the resulting queryset after the union, so you need to filter each queryset before the union, but other than that, everything seems to work in my quick test.

From what I understand, the difference here from MojixCoder's suggestion of using ContentType is that you don't need to maintain a separate definition of this virtual table in your Django models module. In some cases, that can be an advantage, as you don't need to keep the module updated when you get new models you want to include in your query, but in other cases, it can be a disadvantage, because my way has a lot of typing every time you want to use this query, whereas in MojixCoder's example, you define it once, and your queries would be much shorter.

Edit: Using annotate and union can result in the results being out of order. Special care must be taken to ensure this doesn't happen

Answered By - John