How to pass named or numbered parameters to Ecto's fragment?

Hi everyone,

I had a use case where I needed to pass the same few columns to a fragment several times, which would be very cumbersome and quite unreadable by using positional params as described in fragment’s doc. As I couldn’t find anywhere how to use named or numbered params in fragments, I ended up writing my own macro to achieve it. Here’s an example:

# My macro
numbered_fragment("""
 case
   when ?1 > ?2 and ?1 > 100 then 3
   when ?1 == ?2 and ?2 > 100 then 2
   when ?1 < ?2 and ?2 == 100 then 1
   else 0
 end
""", [table.column_a, table.column_b])

# The above would be the same as:
fragment("""
 case
   when ? > ? and ? > 100 then 3
   when ? == ? and ? > 100 then 2
   when ? < ? and ? == 100 then 1
   else 0
 end
""", [t.column_a, t.column_b, t.column_a, t.column_a, t.column_b, t.column_b, t.column_a, t.column_b, t.column_b])

Here’s how the macro is implemented:

defmacro numbered_fragment(sql_string, param_list) do
    pattern = ~r/\?(\d)/

    ordered_params =
      pattern
      |> Regex.scan(sql_string)
      |> Enum.map(fn [_match, capture] ->
        param_position = String.to_integer(capture)
        Enum.at(param_list, param_position - 1)
      end)

    parsed_sql = String.replace(sql_string, pattern, "\?")

    quote do
      fragment(unquote(parsed_sql), unquote_splicing(ordered_params))
    end
  end

I wonder if there is any reason why Ecto doesn’t support something like this by default. Maybe I’m missing something? Maybe it’s a valid feature proposal?

Most likely because not all database support named parameters, like e.g. mysql.

Hm, I don’t think this would be necessarily tied to the database, more of an enhanced way of interpolating the ? placeholders to avoid repeating them several times in cases like the one I described.

Fragments are the escape hatch to write SQL directly in whatever form the database can deal with it. Once you add preprocessing ecto not only needs to be able to parse the SQL given, but also know how to replace things properly to get back to SQL, which is understood by the database in question. Both of those are error prone at best not knowing the database.

I was under the impression that this ? was already part of some sort of preprocessing made by Ecto, to interpolate the binding passed as args to the SQL string. I do understand that enhancing this feature would increase its complexity and error chances, maybe this use case is just not strong enough to justify that.

Anyway, thanks for chatting around with me! I hope my workaround can be useful to someone falling into that in the future.

One reason why it’s not done this way is because the user might think that each named parameter is passed only once to the db while it’s in fact passed as many times as it’s used. Not everyone will have in depth knowledge about the databases at the protocol level.