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.

Iwan1993
  • 1,669
  • 2
  • 17
  • 25
  • Have you tried the `.Case` syntax suggested in this answer: http://stackoverflow.com/questions/4620385/django-annotation-with-nested-filter#answer-35066509 – user2390182 Feb 14 '16 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. – Iwan1993 Feb 14 '16 at 16:18

0 Answers0