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?

You should show your models, probably. Does the QS work without the aggregation? — Lakshman Prasad, Dec 31 '10 at 06:29
Yes, `player.game_objects.extra(select={'week': 'WEEK(games_game.date)'})[0].week` gives `43L` as expected. — Jake, Jan 05 '11 at 07:21
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, Jan 05 '11 at 07:23
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, Mar 03 '11 at 17:10
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, Mar 03 '11 at 17:44

score 15 · Accepted Answer · answered Mar 04 '11 at 15:13

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.

Use YEARWEEK instead of WEEK if your range spans multiple years: — Xerion, Dec 31 '14 at 02:01

score 4 · Answer 2 · answered Mar 03 '11 at 17:42

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

This workaround is exactly how I've done things, but be careful of getting an empty return (i.e. User.ratings is empty), since it will throw an IndexError — jelford, Aug 30 '11 at 09:22
@jelford: I think it just returns None in that case, since at least in SQLite AVG() of an empty set returns NULL. — Mechanical snail, Oct 25 '13 at 22:31

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

2 Answers2

Linked