Reverse foreign relation lookups affecting the rest of the annotate fields in Django-CodePudding

I am running a complex query in Django for a MySQL database and the output is not as expected.

I am using the Django function TruncMonth() to annotate the results per month which works as expected. Once the queryset has been annotated by month I finally add a count of how many orders there were, per month, which again works as expected.

However when I try to get the revenue for the month as well, it somehow affects the count for the total orders per month (See below).

models.py

class Order(models.Model):
    OrderId = models.BigAutoField(primary_key= True)
    OrderDate = models.DateTimeField(default= timezone.now, verbose_name= 'Order Date')
    ...

class OrderItem(models.Model):
    OrderItemId = models.BigAutoField(primary_key= True)
    ArtworkItemId = models.ForeignKey(ArtworkItem, on_delete= models.SET_NULL, null= True, verbose_name= 'Artwork Item')
    OrderId = models.ForeignKey(Order, on_delete= models.CASCADE, verbose_name= 'Order')
    ItemQuantity = models.IntegerField(verbose_name= 'Quantity')

The full query:

Order.objects.annotate(year= ExtractYear('OrderDate'),
                                        month= TruncMonth('OrderDate')).values(
                                        'year', 'month').filter(
                                        year= timezone.now().year, OrderPaid= True).annotate(
                                        orders= Count('OrderId'), revenue= Sum(F('orderitem__ArtworkItemId__ArtworkPrice') * F('orderitem__ItemQuantity')))

# <QuerySet [{'year': 2022, 'month': 'Jul', 'orders': 1, 'revenue': Decimal('180')}, {'year': 2022, 'month': 'Aug', 'orders': 3, 'revenue': Decimal('525')}]>

However when I remove the revenue argument (with the reverse relationship):

Order.objects.annotate(year= ExtractYear('OrderDate'),
                                        month= TruncMonth('OrderDate')).values(
                                        'year', 'month').filter(
                                        year= timezone.now().year, OrderPaid= True).annotate(
                                        orders= Count('OrderId'))

# <QuerySet [{'year': 2022, 'month': 'Jul', 'orders': 1}, {'year': 2022, 'month': 'Aug', 'orders': 2}]>

the orders key-value pair has the correct values: 1 for July and 2 for August.

It seems like as the foreign key has been looked up, it Counts the numbers of OrderItems not Orders. I'm not sure what is going on here and would be grateful for advice.

CodePudding user response：

You can simply do this:

qs_order = Order.objects.filter(
   OrderDate__year=timezone.now().year
   OrderPaid=True
).annotate(
   revenue=F('orderitem__ArtworkItemId__ArtworkPrice') * F('orderitem__ItemQuantity')
)

count = qs_order.count()
total_revenue = qs_order.aggregate(tot=Sum(F('revenue'))['tot']

CodePudding user response：

I will leave my answer here for anyone in the future in case it is helpful.

I managed to solve this problem by setting distinct = True on the Count Function.

My explanation gathered from testing:

It seems that in an annotate() call django then formats the SQL from the perspective of the reverse relation (if any one exists). So for example, in this instance, all the field calls were ultimately evaluated from the position of the reverse relation, orderitem (despite the original call coming from Order).

To be even clearer, every orderitem that was looped over was accessing the OrderId__OrderId attribute, which obiously adds 1 each time. This is instead of coming at it from the other way and counting the one OrderId for the two orderitems. So if there are two items per order, it was initially seen as two orders. Setting distinct = True doesn't add the duplicate OrderId to the count.