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.

9

Is this issue resolved? I am facing a similar problem. I want to filter based on the model field after filtering on the aggregate field. But, django is filtering the model field first and then the aggregate field leading to errors in the retrieved query set.

10

I’m guessing the solution is going to involve some type of subquery expression here, but I’m still having a difficult time understanding what the objective is. It would be extremely helpful if you could provide a minimal code example, with data showing what the results you are looking for, and compared to the results you are getting with your current query.