Opened 16 years ago

Closed 16 years ago

Last modified 12 years ago

#7371 closed (fixed)

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

Reported by: sime Owned by: anonymous
Component: Database layer (models, ORM) Version: dev
Severity: Keywords: qsrf-cleanup ordering foreignkey order_by primary_key
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (8)

comment:1 by Russell Keith-Magee, 16 years ago

Owner: changed from Russell Keith-Magee to anonymous

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

comment:2 by jbronn, 16 years ago

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')

comment:3 by jbronn, 16 years ago

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

comment:4 by jbronn, 16 years ago

Keywords: ordering foreignkey order_by primary_key added
Summary: Missing FROM table from autogenerated SQL throws errorOrdering on a ForeignKey with an existing default ordering and a custom primary key does not generate a JOIN in the SQL.

comment:5 by George Vilches, 16 years ago

Keywords: qsrf-cleanup added

comment:6 by Jacob, 16 years ago

milestone: 1.0

comment:7 by Malcolm Tredinnick, 16 years ago

Resolution: fixed
Status: newclosed

Fixed in [7786].

comment:8 by Jacob, 12 years ago

milestone: 1.0

Milestone 1.0 deleted

Note: See TracTickets for help on using tickets.
Back to Top