Handling Complex QuerySets with Annotations in Django ORM

Using Django Using the ORM
1

Hello everyone,

I’m currently working on a Django project and facing some challenges with complex QuerySets that involve annotations and aggregations. I would appreciate some guidance on the best practices to handle this scenario.

Context:

I have two models: Order and Product. Each Order can contain multiple Products, and each Product has a price and a quantity. I need to generate a report that includes the total number of products sold, the total revenue, and the average revenue per order.

Here are the relevant parts of my models:

python

Copy code

class Product(models.Model):
    name = models.CharField(max_length=255)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.IntegerField()

class Order(models.Model):
    products = models.ManyToManyField(Product, related_name='orders')
    order_date = models.DateTimeField(auto_now_add=True)

Goal:

  1. Calculate the total number of products sold.
  2. Calculate the total revenue.
  3. Calculate the average revenue per order.

What I Have Tried:

I have been able to calculate the total number of products sold and the total revenue using the following QuerySet:

python

Copy code

from django.db.models import Sum, F

total_products_sold = Product.objects.aggregate(total_sold=Sum('quantity'))
total_revenue = Product.objects.aggregate(total_revenue=Sum(F('price') * F(
2

What specifically is your question or issue here?

Are these queries giving you the values you expect, or not? If not, what appears to be the problem?

What assistance are you looking for from us?

Side note: Don’t worry about making things “perfect”. Sometimes, “good enough” is good enough.