id,summary,reporter,owner,description,type,status,component,version,severity,resolution,keywords,cc,stage,has_patch,needs_docs,needs_tests,needs_better_patch,easy,ui_ux 20842,A warning for .order_by() on foreign keys in docs is warranted,Daniele Procida,Daniele Procida,"There's a note on `distinct()` about `order_by()` in the docs: https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.distinct. I think there should also be a similar note for `order_by()`. I don't fully understand what's going on below, but knowing that things like `.order_by('-children__date')` can confuse the ORM would be very useful. How should such a warning be expressed? I will try to create a failing test for this, in case it's deemed a bug worth fixing. I'm not sure it's even a bug though so much as a limitation of the ORM. {{{ # the model class Event(Model): parent = models.ForeignKey('self', related_name='children') date = models.DateField() # in all the examples below, self.series_events is a queryset of Events # ======================================= case one - works as expected ======================================= self.items = self.series_events for item in self.items: print item print self.items.count() # correctly lists the 24 items # correctly returns a count of 24 # SQL for self.series_events SELECT DISTINCT `news_and_events_event`.`id`, `news_and_events_event`.`url`, `news_and_events_event`.`external_url_id`, `news_and_events_event`.`slug`, `news_and_events_event`.`precise_location`, `news_and_events_event`.`access_note`, `news_and_events_event`.`title`, `news_and_events_event`.`short_title`, `news_and_events_event`.`summary`, `news_and_events_event`.`published`, `news_and_events_event`.`in_lists`, `news_and_events_event`.`body_id`, `news_and_events_event`.`image_id`, `news_and_events_event`.`hosted_by_id`, `news_and_events_event`.`importance`, `news_and_events_event`.`content`, `news_and_events_event`.`type_id`, `news_and_events_event`.`parent_id`, `news_and_events_event`.`series`, `news_and_events_event`.`show_titles`, `news_and_events_event`.`display_series_summary`, `news_and_events_event`.`child_list_heading`, `news_and_events_event`.`date`, `news_and_events_event`.`start_time`, `news_and_events_event`.`end_date`, `news_and_events_event`.`end_time`, `news_and_events_event`.`single_day_event`, `news_and_events_event`.`building_id`, `news_and_events_event`.`jumps_queue_on`, `news_and_events_event`.`jumps_queue_e verywhere`, `news_and_events_event`.`lft`, `news_and_events_event`.`rght`, `news_and_events_event`.`tree_id`, `news_and_events_event`.`level` FROM `news_and_events_event` LEFT OUTER JOIN `news_and_events_event_publish_to` ON (`news_and_events_event`.`id` = `news_and_events_event_publish_to`.`event_id`) WHERE (`news_and_events_event`.`in_lists` = True AND `news_and_events_event`.`published` = True AND (`news_and_events_event`.`hosted_by_id` = 2 OR `news_and_events_event_publish_to`.`entity_id` = 2 ) AND `news_and_events_event`.`series` = True ) ORDER BY `news_and_events_event`.`date` ASC, `news_and_events_event`.`start_time` ASC # ======================================= case two - does not work as expected ======================================= # adding .order_by('-children__date') seems to affect the number of items returned! self.items = self.series_events.order_by('-children__date') for item in self.items: print item print self.items.count() # incorrectly lists 319 items # incorrectly returns a count of 319 # SQL for self.series_events.order_by('-children__date') SELECT DISTINCT `news_and_events_event`.`id`, `news_and_events_event`.`url`, `news_and_events_event`.`external_url_id`, `news_and_events_event`.`slug`, `news_and_events_event`.`precise_location`, `news_and_events_event`.`access_note`, `news_and_events_event`.`title`, `news_and_events_event`.`short_title`, `news_and_events_event`.`summary`, `news_and_events_event`.`published`, `news_and_events_event`.`in_lists`, `news_and_events_event`.`body_id`, `news_and_events_event`.`image_id`, `news_and_events_event`.`hosted_by_id`, `news_and_events_event`.`importance`, `news_and_events_event`.`content`, `news_and_events_event`.`type_id`, `news_and_events_event`.`parent_id`, `news_and_events_event`.`series`, `news_and_events_event`.`show_titles`, `news_and_events_event`.`display_series_summary`, `news_and_events_event`.`child_list_heading`, `news_and_events_event`.`date`, `news_and_events_event`.`start_time`, `news_and_events_event`.`end_date`, `news_and_events_event`.`end_time`, `news_and_events_event`.`single_day_event`, `news_and_events_event`.`building_id`, `news_and_events_event`.`jumps_queue_on`, `news_and_events_event`.`jumps_queue_e verywhere`, `news_and_events_event`.`lft`, `news_and_events_event`.`rght`, `news_and_events_event`.`tree_id`, `news_and_events_event`.`level`, T5.`date` FROM `news_and_events_event` LEFT OUTER JOIN `news_and_events_event_publish_to` ON (`news_and_events_event`.`id` = `news_and_events_event_publish_to`.`event_id`) LEFT OUTER JOIN `news_and_events_event` T5 ON (`news_and_events_event`.`id` = T5.`parent_id`) WHERE (`news_and_events_event`.`in_lists` = True AND `news_and_events_event`.`published` = True AND (`news_and_events_event`.`hosted_by_id` = 2 OR `news_and_events_event_publish_to`.`entity_id` = 2 ) AND `news_and_events_event`.`series` = True ) ORDER BY T5.`date` DESC # ======================================= case three - does not work as expected ======================================= # not looping over self.items means it does .count() correctly, but the items returned remain incorrect self.items = self.series_events.order_by('-children__date') # for item in self.items: # print item print items print self.items.count() # returns 24 incorrect items (only five different items are returned, each one multiple times, and others are missing) # correctly returns a count of 24 }}} ",Uncategorized,closed,Documentation,1.5,Normal,fixed,,,Accepted,0,0,0,0,0,0