I've got some simple models below

class Stamping(models.Model):
    created = models.DateTimeField(auto_now_add=True)

    class Meta:
        abstract = True

class Product(Stamping):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='user_product')
    name = models.CharField(max_length=300, unique=True)
    price = models.FloatField()

class GroupProductOrder(Stamping):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='group_product_orders')
    product = models.ForeignKey(Product, on_delete=models.SET_NULL, null=True)
    quantity = models.IntegerField()

class Order(Stamping):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='orders')
    fulfilled = models.BooleanField(default=False)
    products = models.ManyToManyField(GroupProductOrder, blank=True)

    @property
    def total_price(self):
        cost = 0
        for product in self.products.all():
            cost  = product.total_price
        return cost

Now I want to query the database and return some details on a monthly basis. Details like total number of sales for each month and sum of sales for each month. I've tried using annotate but it seems can't pass model property in it. What is the best way to tackle this problem? Example of expected output is {'month': 'October', 'count': 5, 'total': 15}

CodePudding user response：

You can query and annotate with the following statement:

from django.db.models import Sum, F, FloatField
from django.db.models.functions import ExtractMonth

output = (
    Order.objects
    .annotate(
        month=ExtractMonth("created")
    )
    .values("month")
    .annotate(
        count=Sum("products__quantity"),
        total=Sum(
            F("products__quantity") * F("products__product__price"),
            output_field=FloatField()
        )
    )
    .order_by("month")
)