How to use "case-when" in Ecto Queries in elixir?

Question

I have an SQL query like :
SELECT SUM(CASE WHEN <table_name>.status = '2' THEN 1 ELSE 0 END) FROM <table name>.

I want to write the corresponding Ecto Query for the above. Something like:

from t in <table_name>, select: sum(...)

What is the analogy to "case-when" in the above case?

Answer 1

Like the comment said, you can use fragment/1:

query = from t in <Model>, select: fragment("SUM(CASE WHEN status = ? THEN 1 ELSE 0 END)", 2)

If you want to specify the table, this works for me:

query = from t in <Model>, select: fragment("SUM(CASE WHEN ? = ? THEN 1 ELSE 0 END)", t.status, 2)