Filtering on model field after aggregating applies the filter first

Using Django Using the ORM
1

Hi there,

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

model.objects.values(“model_field”).annotate(count_model_field=Count(“model_field”)).filter(model_field=some_value)

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

Thanks.

2

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.

3

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.

4

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.)

5

Sorry, just corrected that!

1 Like
6

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.)

8

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.