Ecto.Query.API.fragment and string interpolation

Questions / Help
,
1

I know this has been asked before, but I still can’t get it to work. I have code like this…

def case_stmt(input) do
  [c1, c2, c3, c4] = generate_clauses(input)

  Ecto.Query.dynamic([foo: a, bar: b], fragment("""
    CASE WHEN ? THEN 1 ELSE 0 END +
    CASE WHEN ? THEN 1 ELSE 0 END +
    CASE WHEN ? THEN 1 ELSE 0 END +
    CASE WHEN ? THEN 1 ELSE 0 END
  """, ^c1, ^c2, ^c3, ^c4))
end

Which works fine when the number of generated clauses is exactly 4. What about when it’s an unknown amount? I want to build up the SQL string using reduce.

There is a really good blog post about making SQL case statements with Ecto:

The problem is that it doesn’t work when you pass it a variable; it only works when you pass a literal.

Any ideas on how to make this work? My use case is user defined queries that are stored in a database. All the user input is validated before making the db records of course. “Just make a case clause for each field the user can search on.” doesn’t work because the schema itself is user defined.

Thanks for the help!

1 Like
2

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

3

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

4

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
2 Likes
5

https://hexdocs.pm/ecto/Ecto.Query.API.html#fragment/1-splicing

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.

1 Like
6

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

7

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… :grimacing:

3 Likes
8

Oh that’s pretty clever. Nice job!

1 Like