按日期分组查询集 [英] Grouping queryset by date

问题描述

我有一个包含日期以及其他一些字段的类.

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> ...]
    } ...
]

我很确定我需要某种注释，但是我不确定100％如何构造它.

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

我最近的是:-

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?!

推荐答案

大多数(如果不是全部) SQL 数据库无法返回分层数据，或者至少没有很多指标.您应该在Django级别进行排序，例如使用 itertools.groupby [Python-doc] :

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')
    )
]

通常，开销不会那么大，因为反序列化对象已经花费了线性时间，并且此 itertools.groupby 也花费了线性时间.

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.

重要的是对键函数进行 .order_by(..)，因为 .groupby(..)不会在分组之前首先查看所有对象:它将分组具有相同键值的元素的子序列".

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.

这篇关于按日期分组查询集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！