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