Django - How do to subtract two annotations with different models-CodePudding

a = Category.objects.annotate(total_products=Count('product'))
b=Order.objects.values('category__name').annotate(total_approve=Sum('approve_quantity')).order_by('-total_approve')

so I want to subtract a - b.

Both annotations works fine but how to reduce the total_products in a category when a specific quantity is approved is the challenge. Is there a different way ?

class Category(models.Model):
    name = models.CharField(max_length=50, blank=True, null=True)
    reorder_level = models.IntegerField(default='0', blank=True, 
        null=True)
    timestamp = models.DateTimeField(auto_now_add=False, auto_now=True, 
        null=True)

class Product(models.Model):
    pro_name = models.CharField(max_length=100, blank=True, null=True)
    category = models.ForeignKey(Category, on_delete=models.CASCADE, blank=True, null=True)
    order = models.ForeignKey('Order',default='', on_delete=models.CASCADE,blank=True, null=True)
    serial_num = models.CharField(max_length=100, blank=True, null=True)
    model_num = models.CharField(max_length=100, blank=True, null=True)
    storage_size = models.CharField(max_length=50, blank=True, null=True)
    memory_size = models.CharField(max_length=50, blank=True, null=True)
    supplier = models.ForeignKey(Supplier, on_delete=models.CASCADE, blank=True, null=True)
    receive_quantity = models.IntegerField(default='0', blank=True, null=True)
    issue_by = models.CharField(max_length=50, blank=True, null=True)
    issue_to = models.CharField(max_length=50, blank=True, null=True)
    last_updated = models.DateTimeField(auto_now_add=False, auto_now=False, null=True) 
    timestamp = models.DateTimeField(auto_now_add=False, auto_now=True, null=True)

class Order(models.Model):
    category = models.ForeignKey(Category, on_delete=models.CASCADE, 
        null=True)
    pro_name = models.ForeignKey(Product, on_delete=models.CASCADE, 
        null=True,related_name='product') 
    staff = models.ForeignKey(User, on_delete=models.CASCADE, null=True)
    order_quantity = models.PositiveIntegerField(null=True)
    department = models.CharField(max_length=50, choices=DEPARTMENT, null=True)
    order_status = models.IntegerField(default=0)
    approve_quantity = models.IntegerField(default='1', blank=True, null=True)
    transaction_id = models.CharField(default=uuid.uuid4().hex[:8].upper(), max_length=50, editable=False)
    timestamp = models.DateTimeField(auto_now_add=False, auto_now=True, null=True)

CodePudding user response：

You can annotate the Categorys with a Subquery expression ^[Django-doc]:

from django.db.models import Count, F, OuterRef, Subquery, Sum

Category.objects.annotate(
    total_products=Count('product'),
    total_approve=Subquery(
        Order.objects.filter(
            category=OuterRef('pk')
        ).values('category').annotate(
            total=Sum('approve_quantity')
        ).order_by('category').values('total')[:1]
    )
).annotate(
    difference=F('total_products')-F('total_approve')
)

The Category objects that arise from this queryset will have an extra attribute .difference that will contain the difference between the number of related Products, and the total of the approved_quantitys for the related Orders.