django: time range based aggregate query

问题:

I have the following models, Art and ArtScore:

class Art(models.Model):
    title = models.CharField()

class ArtScore(models.Model):
    art = models.ForeignKey(Art)
    date = models.DateField(auto_now_add = True)
    amount = models.IntegerField()

Certain user actions results in an ArtScore entry, for instance whenever you click 'I like this art', I save a certain amount of ArtScore for that Art.

Now I'm trying to show a page for 'most popular this week', so I need a query aggregating only ArtScore amounts for that time range.

I built the below query but it's flawed...

popular = Art.objects.filter(
    artscore__date__range=(weekago, today)
).annotate(
    score=Sum('artscore__amount')
).order_by('-score')

... because it only excludes Art that doesn't have an ArtScore record in the date range, but does not exclude the ArtScore records outside the date range.

Any pointers how to accomplish this would be appreciated!

Thanks,

Martin

回答1:

it looks like according to this: http://docs.djangoproject.com/en/dev/topics/db/aggregation/#order-of-annotate-and-filter-clauses what you have should do what you want. is the documentation wrong? bug maybe?

"the second query will only include good books in the annotated count."

in regards to:

>>> Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))

change this to your query (forget about the order for now):

Art.objects.filter(
    artscore__date__range=(weekago, today)
).annotate(
    score=Sum('artscore__amount')
)

now we can say

"the query will only include artscores in the date range in the annotated sum."