Ordering on a ForeignKey with an existing default ordering and a custom primary key does not generate a JOIN in the SQL.

[sime]

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.

[Russell Keith-Magee]

Don't know why this was assigned to me specifically.

[jbronn]

Confirmed this is a bug. From my investigations it appears that a proper join isn't set up due to some of the settings in your models (I think it may be the use of a nonstandard primary key, but this is just a hunch). Here's a workaround until I delve further:

Trip.objects.order_by('origin').select_related('origin')

[jbronn]

My suspicions were confirmed -- if the primary_key=True is omitted from the Loc.code model field, the following SQL is generated:

SELECT "tfr_trip"."id", "tfr_trip"."origin_id" FROM "tfr_trip" INNER JOIN "tfr_loc" ON ("tfr_trip"."origin_id" = "tfr_loc"."id") ORDER BY "tfr_loc"."sort" ASC, "tfr_loc"."code" ASC

[Malcolm Tredinnick]

Fixed in [7786].

[Jacob]

Milestone 1.0 deleted