Queryset combine and annotate

Using Django Using the ORM
1

how to combine and annotate one more queryset in list chain or any other solution


def stock1(request):
    
    queryset_1 = (
        product_items.objects.annotate(purchase=Coalesce(Sum (F("purchase_trans_item__qty")) * F('conversion'),0, output_field=DecimalField()))
    )

    queryset_2 = (
        product_items.objects.annotate(sales=Coalesce(Sum (F("sales_trans_item__qty")) * F('conversion'),0, output_field=DecimalField()))
    )

    result_list=list(chain(queryset_1 , queryset_2)) 


    context={
         'filter':result_list,  
      }
    return render (request,'stock.html',context)

1
11920×1080 125 KB

2

tried this approach

    combined_queryset = queryset_1.union(queryset_2)

    result_list = list(combined_queryset)
3 

def stock1(request):
    
    result_list = []
    products=product_master.objects.select_related('company_name')
    for item in products:
        purchase = product_items.objects.filter(product_name=item).aggregate(purchase=Sum (ExpressionWrapper(F('purchase_trans_item__qty') * F('conversion'), output_field=DecimalField())))['purchase']
        sales = product_items.objects.filter(product_name=item).aggregate(sales=Sum (ExpressionWrapper(F('sales_trans_item__qty') * F('conversion'), output_field=DecimalField())))['sales']
    
        if purchase is not None:
            purchase=purchase
        else:
            purchase=0

       

        if sales is not None:
            sales=sales
        else:
            sales=0
      

        stock= int(purchase)-int(sales)
        result_list.append({'barcode':item.barcode,'product_name':item,'branch_name':item.branch_name, 'purchase':purchase,'sales':sales,'stock':stock})


    print(result_list)
    context={

            'filter':result_list
                
            }
    return render (request,'stock.html',context)

this code return exactly what i need, so do you have any simple code against this