annotate then filter then annotate

Question

The code:

now = datetime.now()
year_ago = now - timedelta(days=365)
category_list = Category.objects.annotate(suma = Sum('operation__value')) \
                                .filter(operation__date__gte = year_ago) \
                                .annotate(podsuma = Sum('operation__value'))

The idea: get sum of each category and sum of one year back.

But this code result only filtered objects; suma is equal to podsuma.

Greg · Accepted Answer · 2012-05-02 21:36:08Z

2

A queryset only produces one query, so all annotations are calculated over the same filtered data set. You'll need to do two queries.

Update:

Do something like this:

In models.py

class Category(models.Model):
    ...
    def suma(self):
        return ...
    def podsuma(self):
        return ...

Then remove the annotations and your for loop should work as is. It'll mean a lot more queries, but they'll be simpler, and you can always cache them.

edited May 2, 2012 at 21:36

answered Apr 30, 2012 at 2:23

Greg

10.4k5 gold badges47 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tomasz Brzezina Over a year ago

Ok - so how to solve the problem? I need it in one queryset - in template there is {for loop}

Greg Over a year ago

I'd write methods on the Category class - I've updated the answer with an example.

Tomasz Brzezina Over a year ago

stackoverflow.com/questions/10380392/… there is the full solution - but thanks for explaining the main problem -

Collectives™ on Stack Overflow

annotate then filter then annotate

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related