django从聚合日期开始获取月份 [英] django getting month from date for aggregation

问题描述

我正尝试在DJango ORM查询电话中获得每月响应的总和：

I am trying to get a sum per month response in a DJango ORM query call:

models.Subscription.objects.all().extra(select={'Month': "EXTRACT(Month FROM Modified)"}).annotate(count_items=Count('Modified'))

我的模型是这样的：

class Subscription(models.Model):
    SType = (
        ('C', 'Civilian'),
        ('E', 'Enlisted'),
        ('O', 'Officer'),
    )
    Subscription_Type = models.CharField(max_length=1, choices=SType)
    Lifetime = models.BooleanField(default=False)
    Member = models.ForeignKey(Member)
    Modified = models.DateField()

    objects = SubscriptionManager()

    def __str__(self):
        return self.Member.first_name + " " + self.Member.last_name + ": " + self.Subscription_Type + "; last modified: " + self.Modified.strftime('%Y-%m-%d')

我正在运行Postgres数据库背景，并确认了修改列。

I am running a Postgres db background, and confirmed the column "Modified".

但是，当我运行查询时，我收到以下错误：

However, when I run the query I get this error:

File "/home/arcee123/.local/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "modified" does not exist
LINE 1: SELECT (EXTRACT(Month FROM Modified)) AS "Month", "ifthq_sub...

我忘记了什么？我试图得到每个月的记录数。谢谢。

Am I forgetting something? I'm trying to get the count of records for each month. Thanks.

推荐答案

您是否尝试查看您的架构？我认为你的列名错误。

Did you try looking at your schema? I think you just got your column name wrong.

假设你使用Djnago> = 1.8，你也可以通过写一个 Func来避免整个问题像这样：

Assuming you are using Djnago>=1.8 you can also avoid the whole issue by writing a Func like this one:

class ExtractMonth(Func):
    template = "EXTRACT(MONTH FROM %(expressions)s)"

    def __init__(self, *expressions, **extra):
        extra['output_field'] = SmallIntegerField()
        super().__init__(*expressions, **extra)

并使用如下：

Subscription.objects.annotate(Month=ExtractMonth('Modified'))\
                .values('Month').annotate(Count('Month'))

这样'Modified' code>是您的模型上的字段的名称，Django会为您解析列名。

This way 'Modified' is the name of the field on your model and Django resolves the column name for you.

在这个1.9票。

这篇关于django从聚合日期开始获取月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！