1

I have the following Django models:

class Order(models.Model):
    ...
    org_slug = models.CharField()

class ProductToOrder(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE)
    qty = models.IntegerField()
    cost = models.FloatField()

I need to sum Orders that grouped by on org_slug and it must have sum of orders that have only one ProductToOrder and ProductToOrder.qty=1

I'm trying to do it in this way

        orders = Order.objects.filter(
            some_filter_here
        ).values('org_slug').annotate(
            order_sum=Subquery(Order.objects.filter(
                org_slug=OuterRef('org_slug')).annotate(
                    product_count=Coalesce(Sum('producttoorder__qty'), 1)
        ).filter(product_count=1).values('org_slug').annotate(
            sum=Sum('producttoorder__cost')
        ).values('sum')[:1]))

But it returns queryset of all orders. For example, if I have 1000 orders and 2 org_slugs, I need to get 2 objects in the query list but it returns 1000. This line where I'm trying to group by queryset in the following Subquery does not work:

.filter(product_count=1).values('org_slug')

I'm tried a lot of different querysets to fix it but it didn't give me any positive result. I see only one way to fix it, I need to create qty field in Order model but I hope, there are other ways to fix it. Can anybody help me with it?

3
  • So what should happen if an Order has no ProductToOrders with qty=1, or multiple? Commented Apr 11 at 15:03
  • What is the difference between Order and AppsOrder? Commented Apr 11 at 15:04
  • If Order has no ProductToOrder with qty=1 or it has multiple ProductToOrder then we don't need to use them in statistics. I use this filter .filter(product_count=1) to sum only orders where ProductToOrder.qty=1. Order and AppsOrder are the same, I just renamed model but forgot rename it in one place of code (I've already renamed it).
    – User Dev
    Commented Apr 11 at 15:15

1 Answer 1

0

I think you are overcomplicating things. You can work with:

from django.db.models import Sum

Order.objects.filter(org_slug=my_slug, producttoorder__qty=1).annotate(
    total=Sum('producttoorder__cost')
)

This will retrieve the Order objects with org_slug=my_slug, and sum up the cost of all related ProductToOrders.

There is however a caveat here: it will also sum up if there are multiple ProductToOrders with qty=1. If you want to omit these, we can work with:

from django.db.models import Count, Exists, OuterRef, Sum

Order.objects.filter(
    ~Exists(ProductToOrder.objects.filter(~Q(qty=1), order_id=OuterRef('pk'))),
    org_slug=my_slug,
    producttoorder__qty=1,
).annotate(total=Sum('producttoorder__cost'), num=Count('producttoorder')).filter(
    num=1
)

This will thus only consider Orders with one ProductToOrder that has qty=1.

5
  • Thank you for your answer. There is one problem with this solution, it will work only for one particular org_slug, and if we have hundred org_slugs, we need to make hundred requests to database in loop to extract all of them. It will be really slow.
    – User Dev
    Commented Apr 12 at 4:38
  • @UserDev: so you aggregate per org_slug, or per Order? Commented Apr 12 at 6:17
  • I need to aggregate per org_slug. For example, if I have 1000 orders with 2 different org_slugs, the result must be the list with 2 elements. Your solution will return 1 element. My solution will return 1000 elements, it just sum to all orders and doesn't apply group by .values('org_slug').
    – User Dev
    Commented Apr 12 at 7:42
  • @UserDev: and thus only work with Orders for that org_slug with exactly one OrderToProduct with qty=1? Commented Apr 12 at 7:43
  • Yes, we must filter to sum only orders that have only one ProductToOrder with qty = 1
    – User Dev
    Commented Apr 12 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.