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

Issue

I have 2 Querysets Sales_order and Proc_order. The only common field in both is the product_id. I want to merge both these query sets to one with all fields.

sales_order has fields product_id,sales_qty,sales_price.

proc_order has fields product_id, proc_qty, proc_price. I want to merge both these to get a queryset which looks like.

combined_report which has fields product_id,sales_qty,sales_price``proc_qty, proc_price.

My final aim is to calculate the difference between the number of products.

I'm using Django 2.1

Solution

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')

Answered By - Pruthvi Barot