Code

Opened 6 years ago

Closed 6 years ago

#9578 closed (invalid)

order_by() removing select_related() joined tables when with extra() query sets

Reported by: marinho Owned by: mtredinnick
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

When using extra + select_related + order_by, the joins are not generated by as_sql().

Look on the examples below:

Without order_by()

qs.extra(
    select={
        'once_title': 'case when feed_id is not null then feedjack_feed.title else podcasts_streamsource.title end'
        },
    ).select_related()

SQL:

(u'SELECT (case when feed_id is not null then feedjack_feed.title else podcasts_streamsource.title end) AS "once_title", "podcasts_channel"."id", "podcasts_channel"."feed_id", "podcasts_channel"."stream_source_id", "podcasts_channel"."image_url", "podcasts_channel"."media_rating", "podcasts_channel"."language", "podcasts_channel"."tags" FROM "podcasts_channel" LEFT OUTER JOIN "feedjack_feed" ON ("podcasts_channel"."feed_id" = "feedjack_feed"."id") LEFT OUTER JOIN "podcasts_streamsource" ON ("podcasts_channel"."stream_source_id" = "podcasts_streamsource"."id") ORDER BY "feedjack_feed"."title" ASC, "podcasts_streamsource"."title" ASC', ())

Now, adding order_by() method

qs.extra(
    select={
        'once_title': 'case when feed_id is not null then feedjack_feed.title else podcasts_streamsource.title end'
        },
    ).select_related().order_by('once_title')

SQL

(u'SELECT (case when feed_id is not null then feedjack_feed.title else podcasts_streamsource.title end) AS "once_title", "podcasts_channel"."id", "podcasts_channel"."feed_id", "podcasts_channel"."stream_source_id", "podcasts_channel"."image_url", "podcasts_channel"."media_rating", "podcasts_channel"."language", "podcasts_channel"."tags" FROM "podcasts_channel" ORDER BY "once_title" ASC', ())

Attachments (0)

Change History (3)

comment:1 Changed 6 years ago by mtredinnick

  • Needs documentation unset
  • Needs tests unset
  • Owner changed from nobody to mtredinnick
  • Patch needs improvement unset
  • Status changed from new to assigned

comment:2 Changed 6 years ago by mtredinnick

The problem description here isn't complete. In the first example, all the "related" selections are left outer joins. Those aren't ever included when you just write select_related(), only when you explicitly include them with select_related("feed", "streamsource") and similar. In short, the first fragment of Python code would not be generating the SQL you pasted unless there's something else going on (like a custom manager).

So can you please post a short, complete (self-contained) example of models that demonstrate the problem. If I add ordering after a select_related() call to a queryset, the related selections are still included, so either you forget that you specified explicit related selections the first time and not the second, or some of the missing information is important for reproducing the problem.

comment:3 Changed 6 years ago by mtredinnick

  • Resolution set to invalid
  • Status changed from assigned to closed

No new information provided, so I'm closing this. Please reopen if the information requested in the previous comment can be provided, but, at the moment, this appears to be a bug in usage, not in functionality.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.