CODEWITHSUNDEEP
pythondjangodjango-querysetmodels
Pablo Viacheslav
Pablo Viacheslav

Reputation: 963

How to combine two Querysets into one from different models?

I need to combine two Querysets from different models into one. The most suggested solutions I've found online are:

To use | and &, but this works only in querysets from the same model

To use chain and list, but this takes away some of the queryset methods, and because of the way the code in my project is structured I cannot change that. I tried it and didn't work.

I read about Q, but it's not clear to me how I can apply it to accomplish what I need.

Basically I need to combine two querysets from different models into a third queryset, not a list. They share some fields names, perhaps that's useful with Q. In example:

queryset_1 = Cars.objects.all().filter(color='red')
queryset_2 = Horses.objects.all()

queryset_3 = queryset_1 + queryset_2

queryset_3.order_by('date')

Upvotes: 1

Views: 7540

Answers (2)

davecaputo
davecaputo

Reputation: 341

If you really need such queryset, this could be a symptom that the models are incorrectly designed, with the risk that any quick fix would probably be more tedious to maintain than reworking your models. My suggestion is to set up a single model which replaces both Car and Horse (even if some fields will remain empty for the instances of each case), with a separate field to allow you identify the two easily. For example:

class Vehicle(models.Model):
    category = models.IntegerField(
        choices=[(10, 'Horse'), (20, 'Car'), (30, 'Bicycle')]
    )
    # other fields here...

Upvotes: 3

Robert Townley
Robert Townley

Reputation: 3574

You can't do it at the database or queryset level unfortunately because those two things don't exist in the same database table. You can do it on the python side (though it's slower and more computationally intensive).

Assuming that both Cars and Horses have a "date" attribute, you can do this:

cars = Cars.objects.all().filter(color='red')
horses = Horses.objects.all()
all_things = list(cars) + list(horses)
sorted_things = sorted(all_things, key=lambda x: x.date)

Another option (which is inefficient on the database level) would be to make them all inherit from the same non-abstract model.

class Item(models.Model):
    date = models.DateTimeFiedl()
    item_type = models.CharField(max_length=255)

    def get_instance(self):
        if self.item_type = 'car':
            return Car.objects.get(id=self.id)
        elif self.item_type == 'horse':
            return Horse.objects.get(id=self.id)

class Car(Item):
    color = models.CharField(max_length=12)

    def save(self, *args, **kwargs):
        self.item_type = 'car'
        super(Car, self).save(*args, **kwargs)

class Horse(Item):
    breed = models.CharField(max_length=25)

    def save(self, *args, **kwargs):
        self.item_type = 'horse'
        super(Horse, self).save(*args, **kwargs)

With that, you can do

items = Item.objects.all().order_by('date')
for item in items:
    print(type(item))  # Always "Item"
    actual_item = item.get_instance()
    if type(actual_item) == Car:
        print("I am a car")
    else:
        print("I am a horse")

While iterating through them grab each specific item as needed (similar to how Wagtail handles pages, you make a convenience method for grabbing an object based on its parent class)

Upvotes: 9

Related Questions