Using .aggregate() on a value introduced using .extra(select={...}) in a Django Query?

Question

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?

Answer 1

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.

Answer 2

Here is an example of the problem and an unideal workaround solution. Take this example model:

class Rating(models.Model):     RATING_CHOICES = (         (1, '1'),         (2, '2'),         (3, '3'),         (4, '4'),         (5, '5'),     )     rating = models.PositiveIntegerField(choices=RATING_CHOICES)     rater = models.ForeignKey('User', related_name='ratings_given')     ratee = models.ForeignKey('User', related_name='ratings_received') 

This example aggregate query fails in the same way as yours because it attempts to reference a non-field value created using .extra().

User.ratings_received.extra(     select={'percent_positive': 'ratings > 3'} ).aggregate(count=Avg('positive')) 

---

One Workaround Solution

The desired value can be found directly by using the aggregate database function (Avg in this case) within the extra value's definition:

User.ratings.extra(     select={'percent_positive': 'AVG(rating >= 3)'} ) 

This query will generate the following SQL query:

SELECT (AVG(rating >= 3)) AS `percent_positive`,        `ratings_rating`.`id`,        `ratings_rating`.`rating`,        `ratings_rating`.`rater_id`,        `ratings_rating`.`ratee_id` FROM `ratings_rating` WHERE `ratings_rating`.`ratee_id` = 1 

Despite the unneeded columns in this query, we can still obtain the desired value from it by isolating the percent_positive value:

User.ratings.extra(     select={'percent_positive': 'AVG(rating >= 3)'} ).values('percent_positive')[0]['percent_positive']