Ecto.Query.API.fragment and string interpolation

The implementation of the macro uses fragments which must be defined at compile time, not at runtime. You cannot pass a list of when/then clauses.

Could you show what exactly do you want to run? How the data is stored and what SQL do you want to build? Maybe we can figure out a different algorithm

Tip: how would you implement dynamic cond in Elixir? You cannot pass conditions dynamically as well.

Why not separate them and do the summing in Elixir? You can also just use select_merge with dummy map keys and then use the resulting map’s values and sum that, similarly to this thread: Aggregate multiple dynamically selected columns in Ecto (without group by) - #3 by zackmichener

def case_stmt(input) do
   clauses = generate_clauses(input)
   bindings = [foo: a, bar: b]

   Enum.reduce(clauses, false, fn clause, query ->
     Ecto.Query.dynamic(
       bindings,
       fragment("CASE WHEN ? THEN 1 ELSE 0 END", ^clause) or ^query
     )
   end)
end

This might help with variable number of elements, where you don‘t know the number of elements at compile time. Not sure if it works with CASE though.

I realize that, yeah. Shame we don’t use a placeholder repo for such forum interactions. Somebody should get to that one day.

Figured it out after chasing down those ideas, but ultimately went a different route (I think).

clauses = generate_clauses(input)

clause_count = Enum.reduce(clauses, nil, fn clause, acc ->
  q = Ecto.Query.dynamic(
    [foo: a, bar: b],
    fragment("CASE WHEN ? THEN 1 ELSE 0 END", ^clause)
  )
  
  if acc do
    Ecto.Query.dynamic([foo: a, bar: b], ^q + ^acc)
  else
    q
  end
end)

Ecto.Query.from(..., select_merge: ^%{clause_count: clause_count})

I need it done in SQL because I’m going to use that count in a where clause.

Very cool! I had been knee deep in Ecto docs for the past couple of days and still managed to miss that. Going to have to tuck that away in my head for later use. I tried using it here with a like a fragment("sum(?)", splice(clauses)) but it didn’t work cuz that’s not how sql sum works…

Oh that’s pretty clever. Nice job!