I have written this query.
sql_query = """
SELECT title, year, runtime, rating,
array_to_string(array_agg(g.name),', ') AS genres,
trailer, homepage FROM shows AS s
LEFT JOIN show_genres AS sg
ON s.id = sg.show_id
LEFT JOIN genres AS g
ON g.id = sg.genre_id
GROUP BY title, year, runtime, rating, trailer, homepage
ORDER BY
(CASE WHEN {sort_direction}=0 THEN {sort_by} END) DESC,
(CASE WHEN {sort_direction}=1 THEN {sort_by} END) ASC
LIMIT 15
OFFSET {page_number};
"""
The problem is with sorting by column. It's generally works for all the columns except genres.
psycopg2.ProgrammingError: column "genres" does not exist
LINE 11: (CASE WHEN 0=0 THEN "genres" END) DESC,
I tried to sort by array_to_string and got same error. Also tried to just sort by genres with statement:
ORDER BY genres DESC
and without CASE statement it works. What could be the problem?
