Convert postgresql ~* as ecto

user_14744038 0
Viewed 112

Is this the correct way to convert postgresql ~* syntax in ecto? When I convert the ecto back into SQL, it is different. It uses or's instead of the original ~*

SQL

SELECT
    col1,
    FROM table1
    WHERE col1 ~* 'AAA|bbb|CcC'

Ecto

from (t1 in table1,
where: ilike(t1.col1, "AAA") or ilike(t1.col1, "bbb") or ilike(t1.col1, "CcC"),
select: %{
 col1: t1.col1
}

The Ecto gets converted back as ORs like so instead of the original WHERE col1 ~* 'AAA|bbb|CcC'

where
    ((sssp0."col1" ilike 'AAA')
    or (sssp0."col1" ilike 'bbb'))
    or (sssp0."col1" ilike 'CcC'))

Is this the same?

ecto elixir
1 Answers

I don't want to speak for the Ecto maintainers, but I believe this isn't included in the standard function set because covering all of the functionality that a database provides would be a virtual nightmare to maintain (I assume). You can, however, create your own API convenience functions to wrap the Postgres operators using fragments under the covers with macros.

In your case, you could create your own API module:

defmodule MyApp.QueryAPI do
  defmacro iregex(left, right) do
    quote do: fragment("? ~* ?", unquote(left), unquote(right))
  end
end

And in the module you want to use it:

defmodule MyApp.Logic do
  import Ecto.Query
  import MyApp.QueryAPI

  def reg_search(term) do
    MyApp.Model.Something
    |> where([s], iregex(s.name, ^term))
    |> MyApp.Repo.all()
  end
end

This is a really neat example of how extensible Ecto really is. I use convenience fragment wrappers all the time to make things more readable and DRY.

user_35429320
Related