Refactor Ecto Fragment into a reusable function

Given the following Ecto query:

  def count_by_owner(daterange) do
    from_query(daterange)
    |> join(:inner, [record: record], admin in Admin, on: record.owner_id == admin.id)
    |> group_by([l, a], selected_as(:full_name))
    |> select([l, a], [selected_as(fragment("(?.\"first_name\" || ' ' || ?.\"last_name\")", a, a), :full_name), selected_as(count(l.id), :count)])
    |> order_by(desc: selected_as(:count))
    |> Repo.all
  end

Does anyone know how I would refactor the fragment bit into a reusable function?

I tried the obvious:

  def count_by_owner(daterange) do
    from_query(daterange)
    |> join(:inner, [record: record], admin in Admin, on: record.owner_id == admin.id)
    |> group_by([l, a], selected_as(:full_name))
    |> select([l, a], [selected_as(fragment_exp(a), :full_name), selected_as(count(l.id), :count)])
    |> order_by(desc: selected_as(:count))
    |> Repo.all
  end

  defp fragment_exp(a) do
    fragment("(?.\"first_name\" || ' ' || ?.\"last_name\")", a, a)
  end

But that throws the ^ bug:

** (Ecto.Query.CompileError) `fragment_exp(a)` is not a valid query expression.

* If you intended to call an Elixir function or introduce a value,
  you need to explicitly interpolate it with ^

And calling fragment_exp via ^fragment_exp(a) or fragment_exp(^a) throws either undefined variable "a" bug or the same ^ bug again.

1 Like

You cannot define it as a function since this expression is evaluated at compile-time. You can use a macro though to define your custom fragment:

defmacro fragment_exp(name) do
    quote do: fragment(~s/(?."first_name" || ' ' || ?."last_name")/, unquote(name), unquote(name))
  end

Here are more examples: https://github.com/felt/geo_postgis/blob/master/lib/geo_postgis.ex.

8 Likes

There’s documentation on how to extract fragments to named macros.

https://hexdocs.pm/ecto/3.11.0/Ecto.Query.API.html#fragment/1-defining-custom-functions-using-macros-and-fragment

In your case I think the fragment can be optimised as well though.

defmacro concat(parts) do
  quote do
    fragment("concat(?)", splice(^parts))
  end
end

select(query, [x], %{fullname: concat([x.firstname, " ", x.lastname])})
5 Likes

Thank you @D4no0 and @LostKobrakai, both are great working solutions!