0

I have a quite common use-case in which i need to count some rows and annotate the result to the base model grouped by the modelinformation itself.

MyModel.objects
    .filter(name='xyz')
    .values('id', 'name')
    .annotate(count=Count('relation__nested_relation', distinct=True))

Now i do not want to count every object in the nested relation, but need to count the objects in a special timespan.

MyModel.objects
    .filter(name='xyz', relation__nested_relation__time__gt=mytime)
    .values('id', 'name')
    .annotate(count=Count('relation__nested_relation', distinct=True))

At this point every instance of MyModel not containing a record for the nested_relation will be dropped. Instead i want to have every MyModel record but with count=0 annotated.

In SQL i would append the filter for the nested_relation in the ON clause of the join. But it seems like django does not support the modification of the on clause. Any other ideas how i can filter my count statement? My database backend is PostgreSQL and Count(When(relation__nested_relation__time__gt=mytime, then=1), distinct=True) raises an SQL syntax exception.

2
  • Have you tried the .Case syntax suggested in this answer: stackoverflow.com/questions/4620385/… Commented Feb 14, 2016 at 16:07
  • Thank you for the suggestion, this does not raise an Syntax Error. But generates wrong results. As i used distinct on my Count Fields, which i dont know how to apply to Sum Fields. Updated questioncode. Commented Feb 14, 2016 at 16:18

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.