Cond in Ecto query DSL

Learning Resources Guides/Tuts/Tips/Info
1

Hi all!

Just want to share a small code snippet which allows writing CASE expressions using macro which is similar to cond.
Here is an example of usage:

import Ecto.Extension.Query.API
import Ecto.Query

from users in "users",
  select: %{
    email: users.email,
    role_label:
      cond_ do
        users.role == "director" -> "DIRECTOR!"
        users.inserted_at < ago(6, "month") -> "OLD!"
        true -> type(users.role, :string)
      end
  }

generated SQL:

SELECT u0."email", CASE WHEN u0."role" = 'director' THEN 'DIRECTOR!' WHEN u0."inserted_at" < $1::timestamp + (-6::decimal::numeric * interval '1 month') THEN 'OLD!' WHEN TRUE THEN u0."role"::varchar END FROM "users" AS u0

and implementation:

defmodule Ecto.Extension.Query.API do
  defmacro cond_(do: block) do
    bindings =
      block
      |> Enum.reduce([], fn
        {:->, _, [[clause], branch]}, acc ->
          [branch, clause | acc]
      end)
      |> Enum.reverse()

    bindings_number = length(bindings)

    sql =
      IO.iodata_to_binary([
        "CASE",
        List.duplicate(" WHEN ? THEN ?", div(bindings_number, 2)),
        " END"
      ])

    quote do
      fragment(unquote(sql), unquote_splicing(bindings))
    end
  end
end

I hope someone will find this useful :slight_smile:

14 Likes
2

Super nice. Once upon a time I was thinking about doing exactly the same magic.

Small suggestion for improvement - define from macro in your module and then traverse the query to replace all cond with your implementation. This should result with clearer API :wink:

3

This is a neat improvement to ecto’s sql!

But the downside to this is that (if I’m following correctly) this improvement would no longer be composable with other ecto DSL improvements since only one person can define from

4

Well, this is my first attempt to implement @hauleth idea. Some kind of composability is still possible

defmodule CustomEctoFrom do
  defmacro __using__(map) do
    quote do
      require Ecto.Query

      defmacro from(expr, kw) do
        kw = CustomEctoFrom.traverse(kw, unquote(map))

        quote do
          Ecto.Query.from(unquote(expr), unquote(kw))
        end
      end
    end
  end

  def traverse({function, args}, map) when is_atom(function) do
    {function, traverse(args, map)}
  end

  def traverse({function, meta, args}, map) do
    function =
      case Map.fetch(map, function) do
        {:ok, aliased_to} -> aliased_to
        :error -> function
      end

    {traverse(function, map), meta, traverse(args, map)}
  end

  def traverse(kw, map) when is_list(kw) do
    for elem <- kw, do: traverse(elem, map)
  end

  def traverse(term, _map), do: term
end

defmodule OtherModuleWithExtensions do
  defmacro concat(a, b) do
    quote do
      fragment("? || ?", unquote(a), unquote(b))
    end
  end
end

defmodule Test do
  import Ecto.Extension.Query.API
  import OtherModuleWithExtensions
  use CustomEctoFrom, %{cond: :cond_, <>: :concat}

  def test do
    from users in "users",
      select: %{
        full_name: users.first_name <> " " <> users.last_name,
        email: users.email,
        role_label:
          cond do
            users.role == "director" -> "DIRECTOR!"
            users.inserted_at < ago(6, "month") -> "OLD!"
            true -> type(users.role, :string)
          end,
        id: users.id
      }
  end
end
1 Like