SQL query to Rails query (Case Statement)

This is my SQL query:

SELECT
  user_id,
  CASE responsible WHEN 1 THEN "true" ELSE "false" END AS responsible
FROM user_departments

How do I write this in rails? I tried a query as below:

Department.first.users.joins(:user_departments).select("users.*, CASE WHEN user_departments.responsible = 1 THEN true ELSE false END AS responsible")

but it does not work. How do I correct this query?

Answer

I don’t know Ruby or your SQL framework very well, but I can see that the query you are running from your Ruby code does not match the literal query which is working. Try this version, which places true and false into single quotes.

Department.first.users.joins(:user_departments).select("users.*, CASE WHEN user_departments.responsible = 1 THEN 'true' ELSE 'false' END AS responsible")

If your SQL database does not have true or false keywords, then your current version might be interpreted as referring to a column called true or false.

For a brief SQL primer, string literals generally should be placed into single quotes, not double quotes. Double quotes are for identifiers, such as things like column and table names. And true or false by themselves as literals would only make sense if your SQL database supported them as keywords.