Chain order by to join

[bbrakenhoff]

Hi,

I am executing the very complex query at the end of this issue :P. The only problem with it is that I cannot chain multiple left joins. But also I cannot chain an order by to these join statements.

It would be nice if you could build this feature for me :).

"SELECT " + WATCHLIST_TABLE + ".*, " + "CASE WHEN " + WATCHLIST_TABLE + "." + WATCHLIST_TYPE + "='" + WatchlistItem.TYPE_MOVIE + "' " + "THEN " + MOVIE_TABLE + "." + MOVIE_TITLE + " " + "ELSE " + SHOW_TABLE + "." + SHOW_TITLE + " " + "END AS watchlist_item_title, " + "CASE WHEN " + WATCHLIST_TABLE + "." + WATCHLIST_TYPE + "='" + WatchlistItem.TYPE_SEASON + "' " + "THEN " + SEASON_TABLE + "." + SEASON_NUMBER + " " + "WHEN " + WATCHLIST_TABLE + "." + WATCHLIST_TYPE + "='" + WatchlistItem.TYPE_EPISODE + "' " + "THEN " + EPISODE_TABLE + "." + EPISODE_SEASON + " " + "ELSE NULL " + "END AS season_number," + EPISODE_TABLE + "." + EPISODE_NUMBER + " AS episode_number " + "FROM " + WATCHLIST_TABLE + " " + "LEFT JOIN " + MOVIE_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_MOVIE_ID + "=" + MOVIE_TABLE + "." + MOVIE_ID_TRAKT + " " + "LEFT JOIN " + SHOW_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_SHOW_ID + "=" + SHOW_TABLE + "." + SHOW_ID_TRAKT + " " + "LEFT JOIN " + SEASON_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_SEASON_ID + "=" + SEASON_TABLE + "." + SEASON_ID_TRAKT + " " + "LEFT JOIN " + EPISODE_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_EPISODE_ID + "=" + EPISODE_TABLE + "." + EPISODE_ID_TRAKT + " " + "ORDER BY " + "CASE WHEN LOWER(SUBSTR(watchlist_item_title,1,4)) = 'the ' " + "THEN SUBSTR(watchlist_item_title,5) ELSE watchlist_item_title END ASC, " + "season_number ASC, episode_number ASC";

[monxalo]

This is duplicate of #41. Track there the status.