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

23
votes

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?

2
pythonmysqldjangodjango-querysetdjango-aggregation
You should show your models, probably. Does the QS work without the aggregation? - lprsd
Yes, player.game_objects.extra(select={'week': 'WEEK(games_game.date)'})[0].week gives 43L as expected. - Jake
My models are fairly complex, this is a simplification of my problem. If it helps I could write a test case with simple models. - Jake
I have a couple ideas for an answer, but whether it would even be useful, and what the appropriate way to specifically implement it would be depends on some specifics of your models and/or DB structure which it seems silly to attempt to infer since you can provide what they actually are (ditto for you, Trey). Could you post them (preferred) or an elided/simplified version of them so I can take a stab at it? - desfido
I just posted an answer containing an example scenario where this is a problem and an workaround solution that is better than using raw SQL but still unideal. - Trey Hunner

2 Answers

15
votes

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.

4
votes

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