Filtering on model field after aggregating applies the filter first

Hi there,

I would love to apply a filter to only keep some specific groups after grouping by and aggregating.


would apply the filtering before the annotations which is not the desired output. Any idea on how to achieve that without raw SQL ?


I frequently find in these types of situations that it’s a lot better to address the specifics than trying to talk about it abstractly. It’s probably going to be easier if you provide a sample model and the results you’re trying to produce from that model.

True, here is an example:

class Example:
profile = models.ForeignKey(Profile, on_delete=models.CASCADE)
score = models.IntegerField(null=True)
date = models.DateField()

I want to get the sum of the score over several days after grouping by profiles, generate a rank based on that aggregated score and then keep only the aggregated score/rank of a particular profile:

window = {“order_by”: F(“progression”).desc()}
Example.objects.values(“profile”).annotate(progression=Sum(“score”)).annotate(updated_rank=Window(expression=DenseRank(), **window)).filter(profile=my_profile)

In reality, the filter is applied before grouping by hence wrong aggregations and rankings.

Questions based upon your sample:

  • What filter? I see no filter in this query.

  • What is “step_count”? That doesn’t seem to be defined here.

(The reason for asking for a complete sample is so that I can try to recreate what you’re seeing, with an eye toward experimenting a little to find a solution.)

Sorry, just corrected that!

1 Like

Ok, I can see the issue that you’re reporting. I’m not immediately seeing any direct solution to this.

Couple more questions for you - what database engine are you using?

Do you already have the raw SQL that would return the desired results? (It might be easier to reverse-engineer the ORM query to match it.)

I suggest you open a new topic for your question. When you do, please include your model and a minimal example of what you’re trying to achieve.