Trouble creating dynamic Ecto query

Questions / Help
,
1

I am trying to build a dynamic query to search different columns of a table.

fields = [:id, :title, :body]

And then I am trying to take these fields and generate a dynamic like query fragment for each field, however I can’t seem to get the first step to work.

Here’s what I have now, how do I write this to work with a the dynamic function:

def lookup(search_term) do
  query = 
    from t in Post
    dynamic([t], like(fragment("CAST(? AS text)", field(t, :id)), ^search_term) )
    select: t

  Repo.all(query)
end
2

You can use enum.reduce to build the query

dynamic([p],
^Enum.reduce(fields, true, fn field, acc →
dynamic([t], like(fragment(“CAST(? AS text)”, field(t, field)), ^search_term)

3

Interesting, I am trying that but am getting an error: is not a valid query expression.

def lookup(search_term)
    fields = [:id, :title, :body]

    query =
      from t in Post,
      where:
        dynamic([p],
          ^Enum.reduce(fields, true, fn field, acc ->
            dynamic([t], like(fragment("CAST(? AS text)", field(t, field)), ^search_term))
          end)
        ),
      select: t

  Repo.all(query)
end
4

I was answering from my phone so I made a typo:


def lookup(search_term)
    fields = [:id, :title, :body]

    query =
      from t in Post,
      where:
        dynamic([p],
          ^Enum.reduce(fields, true, fn field, acc ->
            like(fragment("CAST(? AS text)", field(p, field)), ^search_term))
          end)
        ),
      select: t

  Repo.all(query)
end

Hopefully that works.

1 Like
5

Thank you for the response. I think it may be missing something. I am not sure what the error is:

Compiling 1 file (.ex)

== Compilation error in file lib/tester/test.ex ==
** (Ecto.Query.CompileError) `%Ecto.Query.DynamicExpr{
  fun: fn query ->
    _ = query

    {{:^, [], [0]},
     [
       {Enum.reduce(fields, true, fn field, acc ->
          like(
            fragment(
              "CAST(? AS text)",
              field(p, ^field)
            ),
            ^search_term
          )
        end), :any}
     ], [], %{}}
  end,
  binding: [{:p, [line: 17], nil}],
  file: "/Users/../test.ex",
  line: 17
}` 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 ^

* If you intended to call a database function, please check the documentation
  for Ecto.Query.API to see the supported database expressions

* If you intended to extend Ecto's query DSL, make sure that you have required
  the module or imported the relevant function. Note that you need macros to
  extend Ecto's querying capabilities
6

another typo: end was outside the function

def lookup(search_term)
    fields = [:id, :title, :body]

    query =
      from t in Post,
      where:
        dynamic([p],
          ^Enum.reduce(fields, true, fn field, acc ->
            like(fragment("CAST(? AS text)", field(p, field)), ^search_term)
          end)
        ),
      select: t

  Repo.all(query)
end
1 Like