How to dynamically select a column for a WHERE clause in Ecto?

Questions / Help
1

I would like to simplify my current custom filtering function but I’m having trouble how to dynamically select a particular column in a where clause of an Ecto.Query.

What I have right now is this:

def query_filter(query, %{} = filters) do
  query
  |> first_name(filters[:first_name])
  |> last_name(filters[:last_name])
  # other filters truncated ...
end

defp first_name(query, first_name) do
  query
  |> where([a], ilike(a.first_name, ^"#{first_name}%"))
end

defp last_name(query, last_name) do
  query
  |> where([a], ilike(a.last_name, ^"#{last_name}%"))
end

Now, this works fine but I would like to learn how to do this behavior properly since first_name/2 and last_name/2 are really similar save the particular column being filtered.

Knowing that structs are basically special Map, I thought I could do something like this:

def query_filter(query, %{} = filters) do
  query
  |> like(:first_name, filters[:first_name])
  |> like(:last_name, filters[:last_name])
end

defp like(query, attr, value) do
  query
  |> where([a], ilike(a[attr], ^"#{value}%"))
end

But this yields this error:

** (Ecto.Query.CompileError) `a[attr]` is not a valid query expression
    expanding macro: Ecto.Query.where/3
    lib/ras_directory_api/recruitment/filter.ex:39: RasDirectoryApi.Recruitment.Filter.like/3
    (elixir) expanding macro: Kernel.|>/2
    lib/ras_directory_api/recruitment/filter.ex:39: RasDirectoryApi.Recruitment.Filter.like/3

And my bet is that the reason for this is that a here is not really interpreted as a struct in Ecto’s Macro.

Any suggestions how can I improve my verbose code?

2

You are looking for field/2 function:

https://hexdocs.pm/ecto/Ecto.Query.API.html#field/2

query_params
|> Enum.reduce(User, fn({key, val}, query) -> 
  where(query, [u], ilike(field(u, ^key), ^val))
end)
|> Repo.all()
6 Likes
3

Definitely, it’s field/2 that I was looking for! Thanks @Kham!

1 Like
4

this helped me.