Django

Code

Ticket #7371 (closed: fixed)

Opened 6 months ago

Last modified 5 months ago

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 Assigned to: anonymous
Milestone: 1.0 Component: Database layer (models, ORM)
Version: SVN Keywords: qsrf-cleanup ordering foreignkey order_by primary_key
Cc: Triage Stage: Unreviewed
Has patch: 0 Needs documentation: 0
Needs tests: 0 Patch needs improvement: 0

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.

Attachments

Change History

06/05/08 02:04:27 changed by russellm

  • owner changed from russellm to anonymous.
  • needs_better_patch changed.
  • needs_tests changed.
  • needs_docs changed.

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

06/08/08 12:47:16 changed 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')

06/08/08 12:53:30 changed 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

06/08/08 13:01:35 changed by jbronn

  • keywords set to ordering foreignkey order_by primary_key.
  • 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..

06/10/08 09:16:32 changed by gav

  • keywords changed from ordering foreignkey order_by primary_key to qsrf-cleanup ordering foreignkey order_by primary_key.

06/16/08 12:10:45 changed by jacob

  • milestone set to 1.0.

06/29/08 06:19:45 changed by mtredinnick

  • status changed from new to closed.
  • resolution set to fixed.

Fixed in [7786].

Add/Change #7371 (Ordering on a ForeignKey with an existing default ordering and a custom primary key does not generate a JOIN in the SQL.)




Change Properties
Action