Opened 15 years ago

Closed 15 years ago

#9578 closed (invalid)

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

Reported by: Marinho Brandão Owned by: Malcolm Tredinnick
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: no UI/UX: no

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

Change History (3)

comment:1 by Malcolm Tredinnick, 15 years ago

Owner: changed from nobody to Malcolm Tredinnick
Status: newassigned

comment:2 by Malcolm Tredinnick, 15 years ago

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 by Malcolm Tredinnick, 15 years ago

Resolution: invalid
Status: assignedclosed

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.

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