Django Aggregation on date field-CodePudding

Hi I have two following models

class Store(models.Model):
   store_name = models.CharField(max_length=100)
  
   class Meta:
      db_table = 'stores'

class Sales(models.Model): 
    store =  models.ForeignKey(Store, on_delete=models.CASCADE, related_field='sales')
    product_id = models.BigInteger()
    sale_date = models.DateTimeField()
    total_mrp = models.FloatField()

I am trying to fetch sum(total_mrp) of each store day wise but seem to unable to group by date col in my Sales Table

I am using django_rest_framework I tried to do above using following serializers

class SalesSerializer(serializers.RelatedField):
     queryset = Sales.objects.all()

     def to_representation(self, value):
         data = self.queryset.annotate(day=functions.ExtractWeekDay('sale_date'), total_sale=Sum('total_mrp'))
         return data.values('day', 'total_mrp')

class StoreSerializer(serializers.ModelSerializer):
      primary_sales = SalesSerializer(many=False)

     class Meta:
        model = Store
        exclude = ['id']

The output result doesn't get grouped as I expect. Instead I get same data for primary sales for each store.

CodePudding user response：

self.queryset.extra({'sale_day':"date(sale_date)"}).values('sale_day').annotate(total_sale=Sum('total_mrp'))

or if you want do the aggregation specifcally by the day of the week

self.queryset.annotate(sale_day=functions.ExtractWeek('sale_date')).values('sale_day').annotate(total_sale=Sum('total_mrp'))

Something like this might be able to solve your issue :)