Reputation: 110412
I have the following two models for the title of Movies and TV:
Title
- id
- title
- show_name (FK, Nullable)
TVShow
- id
- title
Here are some example data entries:
Episode_Title
- (1, "Terminator", NULL)
- (2, "ZZZ", 1) --> FK to TVShow (1, "Seinfeld")
- (3, "Abyss", NULL)
Here is how it should be sorted:
- Abyss (sort by episode title if show NULL)
- Seinfeld - ZZZ (sort by show title if show NOT NULL)
- Terminator (sort by title if show NULL)
How would I do this with a django QuerySet? What I have now, which is incorrect --
Title.objects.filter('title', 'show__title')
Upvotes: 1
Views: 314
Reputation: 488614
I think you want:
Title.objects.select_related().extra(
select={'sort_title':"COALESCE(`tv_show`.`title`, `title`.`title`)"},
order_by=['sort_title']
)
So you are adding a "virtual" field to the SELECT
clause, COALESCE
will give you the first non-null value, so sort_title will be the TV show's title if there is one, or the regular title. Then you can sort by the name you give it. The select_related()
is so that the join is done in 1 query, I am not sure what the table names are but you can take it from there...
Upvotes: 2