Opened 7 years ago

Closed 7 years ago

Last modified 4 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: master
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: UI/UX:

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 Changed 7 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Owner changed from russellm to anonymous
  • Patch needs improvement unset

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

comment:2 Changed 7 years ago by 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')

comment:3 Changed 7 years ago by 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

comment:4 Changed 7 years ago by jbronn

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

comment:5 Changed 7 years ago by gav

  • Keywords qsrf-cleanup added

comment:6 Changed 7 years ago by jacob

  • milestone set to 1.0

comment:7 Changed 7 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

Fixed in [7786].

comment:8 Changed 4 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

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