• Ecto: How to build dynamic fragments and case statements

I needed a way to create a dynamic case statement for an order_by/3 call in a Phoenix app using Ecto.

Take the following example:

order_by_lowest_amount(queryable, rates) do
  order_by(queryable, [x], fragment(
    """
    CASE
      WHEN ? = 'USD' THEN ? * ?
      WHEN ? = 'AUD' THEN ? * ?
      # ...
    END ASC
    """,
    x.currency, x.amount, ^Map.get(rates, :USD, 1),
    x.currency, x.amount, ^Map.get(rates, :AUD, 1),
    # ...)
end

As you can see I’ll need to write a very large fragment to cover all currencies in my app. So let’s refactor it!

We can use a macro to dynamically build the fragment which will make the code far more manageable:

defmacro lowest_amount_fragment_sql() do
  sql = Money.known_currencies()
  |> Enum.map(&"WHEN ? = '#{&1}' THEN ? * ?")
  |> Enum.join("\n")

  """
  CASE
    #{sql}
  END ASC
  """
end

defmacro lowest_amount_fragment(rates) do
  arguments = Money.known_currencies()
  |> Enum.map(&[(quote do: x.currency),
                (quote do: x.amount),
                (quote do: ^Map.get(unquote(rates), unquote(&1), 1)))])
  |> List.flatten()
  |> List.insert_at(0, lowest_amount_fragment_sql())

  quote do: fragment(unquote_splicing(arguments))
end

defp order_by_lowest_amount(queryable, rates) do
  order_by(queryable, [x], lowest_amount_fragment(rates))
end

You should always take care with macros, but this should give you a good idea of ways to use macros to help your build more complex queries. Happy coding!

The Author

Dan Schultzer is an active experienced entrepreneur, starting the Being Proactive groups, Dream Conception organization, among other things. You can find him at twitter

Like this post? More from Dan Schultzer

Comments? We would love to hear from you, write us at @dreamconception.