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
7371	Ordering on a ForeignKey with an existing default ordering and a custom primary key does not generate a JOIN in the SQL.	sime	anonymous	"When using .order_by() on a ForeignKey, where the foreign model defines a Meta ordering attribute, the database API fails to include the related table in the FROM clause.

Error raised --

{{{
Exception Type:  	OperationalError
Exception Value: 	(1054, ""Unknown column 'tfr_loc.sort' in 'order clause'"")

}}}

Models --

{{{
class Loc(models.Model):
    ...
    code = models.CharField(max_length=10, primary_key=True, help_text='Airport/destination code for this location, eg SYD')
    ...
    class Meta:
        ordering = ['sort', 'code']
        db_table = 'tfr_loc'
    ...
        
class Trip(models.Model):
    ...
    origin = models.ForeignKey(Loc, related_name='trips_origin')
    ...
    class Meta:
        db_table = 'tfr_trip'
    ...
}}}

Queryset --

{{{
Trip.objects.order_by('origin')
}}}

Generated SQL --

{{{
SELECT `tfr_trip`.`id`, `tfr_trip`.`booking_id`, `tfr_trip`.`origin_id`, `tfr_trip`.`dest_id`, `tfr_trip`.`service_id`, `tfr_trip`.`num_vehicles`, `tfr_trip`.`amount`, `tfr_trip`.`discount_id`, `tfr_trip`.`driver_id`, `tfr_trip`.`vehicle_id`, `tfr_trip`.`pickup`, `tfr_trip`.`dropoff`, `tfr_trip`.`flight_no`, `tfr_trip`.`pickup_address`, `tfr_trip`.`dropoff_address`, `tfr_trip`.`run_id` FROM `tfr_trip` ORDER BY `tfr_loc`.`sort` ASC, `tfr_trip`.`origin_id` ASC
}}}

I think it's nice that it automagically uses the ordering attribute, I guess it just needs to make sure it includes the foreignkey in the FROM clause."		closed	Database layer (models, ORM)	dev		fixed	qsrf-cleanup ordering foreignkey order_by  primary_key		Unreviewed	0	0	0	0	0	0