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

查看:101
本文介绍了在Django Query中使用.extra(select = {...})引入的值使用.aggregate()的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆