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.

Hey, I found myself wishing for named params in Ecto fragments and was referred to this thread. Seeing the resistance to implementing this feature in Ecto itself, I wrote a little library which introduces a macro on top of Ectos fragment.

After use’ing the module, the named_fragment macro is available and can be used like this

named_fragment("coalesce(:left, :right)", left: "example", right: "input")

Using placeholders multiple times is allowed.

named_fragment("coalesce(:a, :b, :a)", a: 1, b: 2)

will expand to

fragment("coalesce(?, ?, ?)", 1, 2, 1)

Feedback very welcome. please play with it and maybe a living example of this pattern helps adoption into Ecto itself :crossed_fingers:

9 Likes

Very nicely done!

1 Like

I made a case to add named params to Ecto itself on the Ecto mailing list here: https://groups.google.com/g/elixir-ecto/c/gEqI9lE3HGE

1 Like

Update: The discussion on the mailing list and a related PR (https://github.com/elixir-ecto/ecto/pull/4239) stalled. We couldn’t come up with a proposal for better fragments that was easy enough to implement/maintain and safe to use to satisfy everyone. Instead José suggested to implement our fragments proposal in a library.

This is what I did, here is the library introducing named and inline ecto fragments: GitHub - tessi/ecto_fragment_extras: Ecto fragment() with named or inline params instead of just ?'s

Both (named and inline) fragment macros expand into plain old ecto fragments. So they offer the same safety and performance if used correctly.

2 Likes