Weird Queryset Result

[Sebastian Heil]

We want to count how often any airport appears as inbound or outbound airport and the sum for a given set of flights for statistics purposes.

flights = .... # flight queryset

Airport.objects.filter(Q(outbound_flights__in=flights) | Q(inbound_flights__in=flight))\
            .annotate(
                flight_count=Count('outbound_flights') + Count('inbound_flights'),
                outbound_flight_count=Count('outbound_flights'),
                inbound_flight_count=Count('inbound_flights'),
            )

Supposed we have around 80 flights, so we expect at most 160 in the counts of any airport.

However, we get around 760 for the total count of the most frequent airport. Other numbers exceed the expected values as well.

What's wrong here?

Note: outbound_flights and inbound_flights are the reverse ForeignKeys of the model Airport and obviously each flight has one arrival and one departure airport.

[Tim Graham]

Please see if ​https://docs.djangoproject.com/en/dev/topics/db/aggregation/#order-of-annotate-and-filter-clauses helps. You might want to try the distinct=True parameter for Count.

This ticket tracker isn't really the place to ask questions like this though. Please see TicketClosingReasons/UseSupportChannels. If the conclusions from those channels indicate this might be a bug in Django, then you may reopen the ticket. Thanks!

[Anssi Kääriäinen]

I believe this to be another instance of the "multiple multivalued joins in the same query produces wrong results for aggregation" problem. I wish there was an easy solution for this.

[Sven R. Kunze]

I believe this to be another instance of the "multiple multivalued joins in the same query produces wrong results for aggregation" problem. I wish there was an easy solution for this.

I remember we having this issue as well in 3 of our products using Django. We solved it by using Python for loops and dicts created from values_list. Not sure if swapping filter and annotate would have helped there.

What I can tell you and what I remember is that it's definitely not intuitive. ;-)