在Django Query中使用.extra（select = {...}）引入的值使用.aggregate（） [英] Using .aggregate() on a value introduced using .extra(select={...}) in a Django Query?

问题描述

我试图得到玩家每周玩的次数，如下所示：

  player。 game_objects.extra（
 select = {'week'：'WEEK（`games_game`.`date`）'} 
）.aggregate（count = Count（'week'））
  

但是Django抱怨说，

  FieldError：无法将关键字'week'解析为字段。选择是：<列出模型字段> 
  

我可以在这样的原始SQL中执行此操作

  SELECT WEEK（date）as week，COUNT（WEEK（date））as count FROM games_game 
 WHERE player_id = 3 
 GROUP BY week 
  

在Django中没有执行原始SQL，是否有一个很好的方法？

解决方案

您可以使用自定义聚合函数来生成查询：

  WEEK_FUNC ='STRFTIME（%%%% W，％s）'＃对于mysql使用'WEEK（％s）'
 
 class WeekCountAggregate（models.sql.aggregates.Aggregate）：
 is_ordinal = True 
 sql_function ='WEEK'＃unused 
 sql_template =COUNT（％s）％（WEEK_FUNC.replace（'%%'，'%%%%'）％' ％（field）s'）
 
 class WeekCount（models.aggregates.Aggregate）：
 name ='Week'
 def add_to_query（self，query，alias，col，source ，is_summary）：
 query.aggregates [alias] = WeekCountAggregate（col，source = source，
 is_summary = is_summary，** self.extra）
 
 
>>> game_objects.extra（select = {'week'：WEEK_FUNC％'games_game。date'}）。values（'week'）。annotate（count = WeekCount（'pk'））
 raw query 。
 
I'm trying to get the count of the number of times a player played each week like this:
player.game_objects.extra(
    select={'week': 'WEEK(`games_game`.`date`)'}
).aggregate(count=Count('week'))
But Django complains that
FieldError: Cannot resolve keyword 'week' into field. Choices are: <lists model fields>
I can do it in raw SQL like this
SELECT WEEK(date) as week, COUNT(WEEK(date)) as count FROM games_game
WHERE player_id = 3
GROUP BY week
Is there a good way to do this without executing raw SQL in Django?
解决方案
You could use a custom aggregate function to produce your query:
WEEK_FUNC = 'STRFTIME("%%%%W", %s)' # use 'WEEK(%s)' for mysql

class WeekCountAggregate(models.sql.aggregates.Aggregate):
    is_ordinal = True
    sql_function = 'WEEK' # unused
    sql_template = "COUNT(%s)" % (WEEK_FUNC.replace('%%', '%%%%') % '%(field)s')

class WeekCount(models.aggregates.Aggregate):
    name = 'Week'
    def add_to_query(self, query, alias, col, source, is_summary):
        query.aggregates[alias] = WeekCountAggregate(col, source=source, 
            is_summary=is_summary, **self.extra)


>>> game_objects.extra(select={'week': WEEK_FUNC % '"games_game"."date"'}).values('week').annotate(count=WeekCount('pk'))
But as this API is undocumented and already requires bits of raw SQL, you might be better off using a raw query.

                        
这篇关于在Django Query中使用.extra（select = {...}）引入的值使用.aggregate（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！