How to count model Objects based on date?

Using Django Using the ORM
1

I am trying to get/count how many orders that were made in a month and return it as a response to the api view, i have written some logic to do this, but the issue is this: It does not count how many total orders were made in a month, but instead add new orders for the same month.

To be more explicit, look at the reponse below:

This is the query i wrote to get and count the orders based on the months

from django.db.models.functions import ExtractMonth

orders = Orders.objects.annotate(month=ExtractMonth("date")).values("month").annotate(count=Count("id")).values("month", "count")
print("orders",  orders)

This is th response

 "orders": [
        {
            "month": 1, #january
            "count": 1  #one order
        },
        {
            "month": 1,  #january
            "count": 1   #one order
        },
        {
            "month": 3, #march
            "count": 1  #one order
        },
        {
            "month": 3, #march
            "count": 1  #one order
        },
  

        {
            "month": 4, #April
            "count": 1 # one order
        },
        {
            "month": 4,
            "count": 1
        }
    ]

Look at the first month januray, i want it to return the response like this

{
            "month": 1, # january
            "count": 2  # two orders
        },

I also have this logic where i loop through the orders then append the month and total_orders to the variable

Complete Code

from django.db.models import Count, Avg
from django.db.models.functions import ExtractMonth
import calendar


@api_view(('GET', ))
def ChartAPIFBV(request):
    orders = Orders.objects.annotate(month=ExtractMonth('date'
            )).values('month').annotate(count=Count('id'
            )).values('month', 'count')
    month = []
    total_orders = []

    for i in orders:
        month.append(calendar.month_name[i['month']])
        total_orders.append(i['count'])

    data = {'month': month, 'total_orders': total_orders,
            'orders': orders}

    return Response(data)

Complete Respose

{
    "month": [
        "January",
        "January",
        "March",
        "March",
        "March",
        "March",
        "April",
        "April"
    ],
    "total_orders": [
        1,
        1,
        1,
        1,
        1,
        1,
        1,
        1
    ],
    "orders": [
        {
            "month": 1,
            "count": 1
        },
        {
            "month": 1,
            "count": 1
        },
        {
            "month": 3,
            "count": 1
        },
        {
            "month": 3,
            "count": 1
        },
        {
            "month": 3,
            "count": 1
        },
        {
            "month": 3,
            "count": 1
        },
        {
            "month": 4,
            "count": 1
        },
        {
            "month": 4,
            "count": 1
        }
    ]
}
2

I think you want to use aggregate here, not annotate. I think by changing that, you can also remove the second values clause.
(That’s just a quick guess, I’m not 100% sure right off-hand.)

What does your Order model look like?

3

I tried using agreegate method and this is the error that i got month is not an aggregate expression 

class Orders(models.Model):
    seller = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name="seller")
    buyer = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name="buyer")
    
    service = models.ForeignKey(Service, on_delete=models.SET_NULL, null=True, related_name="service_orders")
    description = models.CharField(max_length=1000, null=True, blank=True)
    price = models.DecimalField(default=0.00, max_digits=12, decimal_places=2)

    status = models.CharField(max_length=100, choices=JOB_STATUS, default='processing')
    date = models.DateTimeField(auto_now_add=True)

update

I hava actually worked on this before, but it using only pure django and templates not restframework, everything works fine with django but when i try using django restframework that when this issue starts happening, below is the code i used with django.

@login_required
def customer_dashboard(request):
    orders = CartOrder.objects.annotate(month=ExtractMonth("order_date")).values("month").annotate(count=Count("id")).values("month", "count")
    month = []
    total_orders = []

    for i in orders:
        month.append(calendar.month_name[i["month"]])
        total_orders.append(i["count"])

    print("total_orders: ",  orders)
    print("month: ",  month)

    context = {
        "orders": orders,
        "month": month,
        "total_orders": total_orders,
    }
    return render(request, 'core/dashboard.html', context)

if i print orders it display it accordingly

[
   {
      "month":1,
      "count":1
   },
   {
      "month":3,
      "count":18
   },
   {
      "month":6,
      "count":1
   },
   {
      "month":7,
      "count":1
   },
   {
      "month":9,
      "count":1
   },
   {
      "month":10,
      "count":2
   },
   {
      "month":11,
      "count":6
   },
   {
      "month":12,
      "count":1
   }
]

I don’t know if the issue is because i am using django restframework

4

Without knowing the data and the template being rendered, I can’t comment on what your non-DRF version is doing. Also, you’re referencing a different model (CartOrder) in your non-DRF version.

Unfortunately, I can’t recreate the symptoms you are describing here from the information provided so far.

If I run the following query, I get the expected results:

orders = Orders.objects.annotate(month=ExtractMonth('date')
            ).order_by('month').values('month').annotate(count=Count('id'))

(Adding or removing the extra values clause on the end of this does not affect the results.)

That implies to me that there’s something else involved here.

Do you have a custom Model Manager for this model? Any settings in the Meta class for that model?

5

For some reasons that i do not know it actually wokrs by removing the extra .values("month", "count") which you said i should remove and also adding .order_by("month"). i do not know why the .order_by("month") makes it to work, i would really like to know.

 orders = 
       Order.objects
        .filter(vendor=request.user.vendor, payment_status="paid")
        .annotate(
            month=ExtractMonth("date")
        )
        .values("month")
        .annotate(
            count=Count("id"),
        )
        .order_by("month")
    
    
    monthNumber=[]
    totalOrders=[]
    amountTotal=[]


    for d in output:
        monthNumber.append(calendar.month_name[d['month']])
        totalOrders.append(d['count'])
``
6

It’s all documented at Values, particularly the section for interaction with order_by. These docs also reference distinct()