CODEWITHSUNDEEP
pythondjango
gonczor
gonczor

Reputation: 4146

Django group by and count

I am trying to perform a grouping on my model which looks like this:

class Restaurant(models.Model):
    pass

class Order(models.Model):
    created = models.DateTimeField(auto_now_add=True)
    restaurant = models.ForeignKey('Restaurant')

Now I want to know how many orders were created each day. This means I need to get date from DateTime field and then a count.

If it's relevant, I'm getting Order queryset in this way:

restaurant = Restaurant.objects.get(id=request.data['restaurant_id'])
orders = restaurant.order_set.filter(created__lte=some_date)

Now, how can I get what I want from orders? I have tried things like:

orders.values('created').annotate(Count('created'))

Using TruncDate and so on.

I am using Python 2 and Django 1.11.

EDIT To better express my intentions. I want something that I would achieve in pure python with something like this:

orders_by_date = {}
for order in orders:
    if orders_by_date.get(datetime.date(orders[0].created.year, orders[0].created.month, orders[0].created.day):
        orders_by_date[datetime.date(orders[0].created.year, orders[0].created.month, orders[0].created.day)] += 1
    else:
        orders_by_date[datetime.date(orders[0].created.year, orders[0].created.month, orders[0].created.day)] = 1

EDIT2 I was able to successfully display count for a single date:

orders.filter(created__date=datetime.date(2018, 6, 8)).aggregate(Count('id'))

Gave me {'id__count': 3}. Now It would be perfect to group by all dates, not just this single one.

Upvotes: 5

Views: 7403

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477854

You can fetch the data in one query with:

from django.db.models import DateField, Sum
from django.db.models.functions import Cast

query = Order.objects.filter(
    restaurant=some_restaurant
).annotate(
    create_date=Cast('created', DateField())
).values('create_date').annotate(
    id_count=Count('id')
).order_by('create_date')

This will return a QuerySet of dictionaries like:

<QuerySet [{'create_date': datetime.date(2017, 1, 31), 'id_count': 14}, 
           {'create_date': datetime.date(2017, 2, 2), 'id_count': 25}, 
           {'create_date': datetime.date(2017, 2, 9), 'id_count': 13},
           {'create_date': datetime.date(2017, 2, 10), 'id_count': 2}, 
           {'create_date': datetime.date(2017, 2, 16), 'id_count': 17},
           {'create_date': datetime.date(2017, 2, 17), 'id_count': 89}, 
           {'create_date': datetime.date(2017, 2, 20), 'id_count': 20},
           {'create_date': datetime.date(2017, 2, 23), 'id_count': 18}, 
           {'create_date': datetime.date(2017, 2, 24), 'id_count': 20},
           {'create_date': datetime.date(2017, 2, 28), 'id_count': 20}, 
           {'create_date': datetime.date(2017, 3, 1), 'id_count': 3},
           {'create_date': datetime.date(2017, 3, 3), 'id_count': 9}, 
           {'create_date': datetime.date(2017, 3, 7), 'id_count': 9},
           {'create_date': datetime.date(2017, 3, 9), 'id_count': 1}, 
           {'create_date': datetime.date(2017, 3, 10), 'id_count': 7}, 
           {'create_date': datetime.date(2017, 3, 14), 'id_count': 2}, 
           {'create_date': datetime.date(2017, 3, 15), 'id_count': 7},
           {'create_date': datetime.date(2017, 3, 17), 'id_count': 9}, 
           {'create_date': datetime.date(2017, 3, 22), 'id_count': 2},
           {'create_date': datetime.date(2017, 3, 24), 'id_count': 8},
           '...
           (remaining elements truncated)...']>

(added formatting)

Behind the curtains, it will generate a query like:

SELECT CAST(`order`.`created` AS date) AS `create_date`,
       COUNT(`order`.`id`) AS `id_count`
FROM `order`
WHERE `order`.`restaurant_id` = 123
GROUP BY CAST(`order`.`created` AS date)
ORDER BY `create_date` ASC

(where 123 is here a sample restaurant id).

So you can then for instance iterate over the result and construct a JSON object, etc.

We can for example translate it into a dictionary that maps datetime.date objects to counts by iterating over the query with dictionary comprehension:

result = { t['create_date']: t['id_count'] for t in query }

Note that dates without any Order will not be part of the queryset (nor of the result dictionary, this is logical, since we take the Order table as "root", and if there are no rows, then there will be no output)

Upvotes: 2

17slim
17slim

Reputation: 1243

You should be able to do orders.filter(created__date=datetime.date(2018, 6, 8)).values('id__count'), which would give you a list of dictionaries: [{'id__count': 3},{'id__count': 1},...].

Here are the docs

Edit: Try annotating then getting the values:

orders_with_counts = orders.annotate(Count('id'))
dates_and_counts = orders_with_counts.values_list('created__date','id__count')
dct = dict(dates_and_counts)

dates_and_counts would be a list of tuples: [(date1,count1),(date2,count2)...]. dct would be that converted to a dictionary.

Upvotes: 2

Related Questions