0

I've been trying to rework a query and I can't figure out the solution. Here is the setup (oversimplified):

An object OrderLine with a quantity and a product, and the product itself with a stock

class Product(models.Model):
    inventory_quantity = models.IntegerField()

class OrderLine(models.Model):
    product_id = models.ForeignKey(Product, on_delete=models.CASCADE)
    quantity = models.IntegerField()

What I want to do is annotate Product to know the predicted inventory and use it later in the code. I had this code that was working in Django 2:

subquery_projected_product = OrderLine.filter(
            product_id__in=OuterRef('id')
        ).annotate(
            qty=ExpressionWrapper(
                F('product__inventory_quantity') -
                OrderLine.sum_quantity_sql(),
                output_field=IntegerField()
            )
        ).values('qty')

products_queryset = Product.objects.all().
            annotate(
                nb_projected=Subquery(
                    subquery_projected_product,
                    output_field=IntegerField()
                )
        )

But after Django switch to version 3 I ran into this: https://code.djangoproject.com/ticket/31135.

If I understand correctly, this is not the correct way to do the request and it`s not supported anymore.

So to put it simple, how can I, for each product, annotate the sum of quantity of related orderlines ?

Thanks and have a good day.

Rfayolle
  • 3
  • 4
  • Why can `product_id__in=OuterRef('id')` simply not be `product_id=OuterRef('id')`? – Abdul Aziz Barkat Jun 08 '22 at 15:56
  • Indeed. I was reworking the code of another guy so I thought the `_in` was mandatory and I tried to reproduce it. But after your comment I re-checked the whole logic and it's not needed, so I can remove it and the code works fine. Thanks @AbdulAzizBarkat – Rfayolle Jun 08 '22 at 18:54

1 Answers1

0

I don't think you need to use a SubQuery for this, you can just use plain annotations

from django.db.models import Sum, F

Product.objects.annotate(
    order_total=Sum('orderline__quantity', default=0)
).annotate(
    projected=F('inventory_quantity') - F('order_total')
)
Iain Shelvington
  • 31,030
  • 3
  • 31
  • 50
  • Thanks for the suggestion. I tried to do it but Django do not understand how to link `orderline__quantity` to orderline. However I fixed the issue with the comment above. Unfortunately I won't be able to give an answer to my question to someone who really needs to migrate his `_in=OuterRef()` to django 3 – Rfayolle Jun 08 '22 at 18:57