How can I join multiple models in Django into a virtual table?-CodePudding

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?

CodePudding user response：

I think you have two options:

using itertools.chain:

from itertools import chain


cows = Cow.objects.all()
pigs = Pig.objects.all()
horses = Horse.objects.all()

livestock_list = sorted(
    chain(cows, pigs, horses), 
    key=lambda livestock: livestock.created_at, reverse=True)
)

using contenttypes:

from django.contrib.contenttypes.models import ContentType
from django.contrib.contenttypes.fields import GenericForeignKey


class Livestock(models.Model):
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField()
    content_object = GenericForeignKey('content_type', 'object_id')
    created = models.DateTimeField(auto_now_add=True)

    class Meta:
         ordering = ['-created']

Now you can query Livestock model like any other model in Django, but you can have a foreign key that can refers to n models. that's what contenttypes do.

Livestock.content_object gives you what you want in your case it can be Cow, Pig or Horse.

Just remember to add objects to Livestock model after you create horse, etc instances. you need to add them in 2 models actually. you can do it with signals.

I think the second solution is better.

CodePudding user response：

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.