Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#26178 closed Uncategorized (invalid)

Weird Queryset Result

Reported by: Sebastian Heil Owned by: nobody
Component: Database layer (models, ORM) Version: 1.9
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (3)

comment:1 Changed 4 years ago by Tim Graham

Resolution: invalid
Status: newclosed

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!

comment:2 Changed 4 years ago by 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.

comment:3 Changed 4 years ago by 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 in 3 of our products as well. We solved it by using Python for loops and dicts created from values_list. Cannot remember 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. ;-)

Last edited 4 years ago by Sven R. Kunze (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top