Django query left join, sum and group by

I have a model:

class Product(models.Model):
    name = models.CharField(max_length=100)

class Sales(models.Model):
    product_id = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='products')
    date = models.DateTimeField(null=True)
    price = models.FloatField()

How do I return data as the following sql query (annotate sales with product name, group by product, day and month, and calculate sum of sales):

select p.name
    , extract(day from date) as day
    , extract(month from date) as month
    , sum(s.price)
from timetracker.main_sales s
left join timetracker.main_product p on p.id = s.product_id_id
group by month, day, p.name;

Thanks, If only ORM was as simple as sql... Spent several hours trying to figuring it out...

PS. Why when executing Sales.objects.raw(sql) with sql query above I get "Raw query must include the primary key"

You can annotate with:

from django.db.models import Sum
from django.db.models.functions import ExtractDay, ExtractMonth

Product.objects.values(
    'name',
    month=ExtractDay('products__date')
    day=ExtractDay('products__date'),
).annotate(
    total_price=Sum('products__price')
).order_by('name', 'month', 'day')

Note: Normally one does not add a suffix …_id to a ForeignKey field, since Django will automatically add a "twin" field with an …_id suffix. Therefore it should be product, instead of product_id.

Note: The related_name=… parameter [Django-doc] is the name of the relation in reverse, so from the Product model to the Sales model in this case. Therefore it (often) makes not much sense to name it the same as the forward relation. You thus might want to consider renaming the products relation to sales.

Back to Top