Grouping queryset by date

Question

I have a class which contains a date as well as some other fields.

What I'm trying to figure out is a query which will return me each of the items, grouped by the date.

So, given the following class:-

class Item(models.Model):
    item_date = models.DateField()
    <other fields>

I'd like to get something like this:-

[
    {
        'item_date': datetime.date(2018, 9, 12),
        'items': <Queryset [<Item: item1>, <Item: item17> ...]
    }, {
        'item_date': datetime.date(2018, 9, 13),
        'items': <Queryset [<Item: item2>, <Item: item33> ...]
    }, {
        'item_date': datetime.date(2018, 9, 14),
        'items': <Queryset [<Item: item34>, <Item: item37> ...]
    } ...
]

I'm pretty sure I need some sort of annotation but I'm not 100% sure how to structure it.

The closest I've got is:-

Item.objects.values('item_date').annotate(Count('id')).order_by('item_date')

but that just gives me the counts of how many Items are on each date - not the actual Items (obviously!).

What do I need instead of that Count? Is this even possible?!

Willem Van Onsem · Accepted Answer · 2018-10-30T19:13:12.690

Most (if not all) SQL databases can not return hierarchical data, or at least not without a lot of trics. You should do the ordering at the Django level, for example with itertools.groupby [Python-doc]:

from itertools import groupby
from operator import attrgetter

result = [
    {'item_date': k, 'items': list(vs)}
    for k, vs in groupby(
        Item.objects.all().order_by('item_date'),
        attrgetter('item_date')
    )
]

Typically the overhead will not be that significant, since deserializing the objects already takes linear time, and this itertools.groupby takes linear time as well.

It is important to .order_by(..) the key function, since .groupby(..) does not first look to all objects before grouping: it groups "subsequences" of elements with the same key value.

Grouping queryset by date

1 Answers1

Linked