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?

Frey

You probably can do it using fragments -> hexdocs.pm/ecto/Ecto.Query.API.html#fragment/1 – Woodprint

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)

Patnode

This works but just a small question...how can I associate a table name with status like "table_name".status in fragment? – Frey

Answer 2

You can also leverage on macros to extend Ecto query language:

defmacro case_when(condition, do: then_expr, else: else_expr) do
  quote do
    fragment(
      "CASE WHEN ? THEN ? ELSE ? END",
      unquote(condition),
      unquote(then_expr),
      unquote(else_expr)
    )
  end
end

Then use it like this:

query = from t in <Model>,
  select: case_when t.status == 2
    do 1
    else 0
  end

Zeniazenith