CODEWITHSUNDEEP
pythondjango
Narnik Gamarnik
Narnik Gamarnik

Reputation: 1111

Get a list of object created for the last days grouped by day

I need to get a list of objects that have been created in the last few days, grouped by day, in order to display the data in a chart.

I have this model:

class Model(Base):
    ...
    created = DateTimeField(default=timezone.now(), editable=False)
    ...

Now I do this:

Model.objects.filter(
    created__date__lte=today,
    created__date__gte=today-delta
).values('created').annotate(count=Count('id'))

But, I get the following queryset:

<SoftDeletableQuerySet [{'created': datetime.datetime(2018, 10, 15, 13, 5, 35, 208157, tzinfo=<UTC>), 'count': 1}, {'created': datetime.datetime(2018, 10, 15, 13, 5, 35, 297617, tzinfo=<UTC>), 'count': 1}, {'created': datetime.datetime(2018, 10, 15, 13, 5, 35, 385555, tzinfo=<UTC>), 'count': 1}, {'created': datetime.datetime(2018, 10, 15, 13, 5, 35, 474287, tzinfo=<UTC>), 'count': 1}, {'created': datetime.datetime(2018, 10, 15, 13, 5, 35, 507464, tzinfo=<UTC>), 'count': 1}, {'created': datetime.datetime(2018, 10, 15, 13, 5, 35, 552092, tzinfo=<UTC>), 'count': 1}, {'created': datetime.datetime(2018, 10, 15, 13, 5, 35, 585314, tzinfo=<UTC>), 'count': 1}, {'created': datetime.datetime(2018, 10, 15, 13, 5, 35, 618656, tzinfo=<UTC>), 'count': 1}, {'created': datetime.datetime(2018, 10, 15, 13, 5, 35, 652501, tzinfo=<UTC>), 'count': 1}, {'created': datetime.datetime(2018, 10, 15, 13, 5, 35, 696849, tzinfo=<UTC>), 'count': 1}]>

As you can see, the objects were grouped by creation date, but this field is DateTimeField, not DateField, therefore, grouped in milliseconds.

I'm trying to do this:

Model.objects.filter(
    created__date__lte=today,
    created__date__gte=today-delta
).values('created__date').annotate(count=Count('id'))

But I get the following error:

FieldError: Cannot resolve keyword 'date' into field. Join on 'created' not permitted.

I think that what I need is quite common, and there must be some kind of DRY way to do this.

Upvotes: 1

Views: 64

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476584

You can use an .annotate(..) with an TruncDate [Django-doc] expression to truncate to the date:

from django.db.models.functions import TruncDate

Model.objects.filter(
    created__date__lte=today,
    created__date__gte=today-delta
).annotate(
    day=TruncDate('created')
).values('day').annotate(
    count=Count('id')
).order_by('day')

The .order_by is necessary, since otherwise you do not "force" Django to use a GROUP BY, and thus the Count('id') will not contain the total number of items for that specific day.

Upvotes: 2

Related Questions