Using Django How to Combine Two Queries from Separate Models into One Query

Using django how can I combine two queries from separate models into one query?

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.

How to merge two different querysets with one common field in to one in django?

You can try this way to capture all the values.

from django.db.models import Subquery, OuterRef, FloatField
from django.db.models.functions import Cast

subquery_qs = proc_order_qs.filter(product_id=OuterRef('product_id')

combined_qs = sales_order_qs.annotate(
    proc_qty = Cast(Subquery(subquery_qs.values('proc_qty')[:1]), output_field=FloatField()),
    proc_price = Cast(Subquery(subquery_qs.values('proc_price')[:1]), output_field=FloatField()))

And then you can get all the values in combined_qs

combined_qs.values('product_id','sales_qty','sales_price','proc_qty', 'proc_price')

How can I combine two or more querysets in a Django view?

Concatenating the querysets into a list is the simplest approach. If the database will be hit for all querysets anyway (e.g. because the result needs to be sorted), this won't add further cost.

from itertools import chain
result_list = list(chain(page_list, article_list, post_list))

Using itertools.chain is faster than looping each list and appending elements one by one, since itertools is implemented in C. It also consumes less memory than converting each queryset into a list before concatenating.

Now it's possible to sort the resulting list e.g. by date (as requested in hasen j's comment to another answer). The sorted() function conveniently accepts a generator and returns a list:

result_list = sorted(
    chain(page_list, article_list, post_list),
    key=lambda instance: instance.date_created)

If you're using Python 2.4 or later, you can use attrgetter instead of a lambda. I remember reading about it being faster, but I didn't see a noticeable speed difference for a million item list.

from operator import attrgetter
result_list = sorted(
    chain(page_list, article_list, post_list),
    key=attrgetter('date_created'))

How to combine two Querysets into one from different models?

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)

Merge two queries in one single query in django

I think you're making this a bit more complicated than necessary -- the Django ORM handles much of this for you, thanks to that foreign-key relationship. Given an ID for ModelB, the ID for the other ModelB with the same ModelA but where base=True is just:

 ModelB.objects.get(id=modelb_pk).modela.modelb_set.get(base=True).id

Why does this work?

1. Because ModelB has a many-to-one relationship with ModelA, we can call .modela on an instance of ModelB to get the corresponding ModelA.
2. Conversely, given an instance of ModelA, calling .modelb_set returns all ModelB records associated with ModelA.
3. We can then call .get/.filter on modelb_set just like we would with ModelB.objects.

How can i combine these two Django annotated queries into one?

You can pass a filter to Sum to restrict which values are used, you can then have two filtered annotations in the same query

from django.db.models import Sum, Q
Users.objects.annotate(
    likes=Sum('comments__likedislike', filter=Q(comments__likedislike__gt=0)),
    dislikes=Sum('comments__likedislike', filter=Q(comments__likedislike__lt=0))
)

---

Related Topics