I have a dynamic query engine which is also handling search functionality. I have managed to make it dynamic in the sense that I pass the search fields as a list of atoms and up until now I was handling only single tables. Now I have a join of three tables and I ideally I want to be able to search in all of them by being more specific in the search fields that I pass.
Here is the function that until now worked for single tables:
search_fields=[ :description, :price]
defp single_table_search(query, search_term, search_fields) do
or_conditions =
Enum.reduce(search_fields, nil, fn field, dynamic_acc ->
condition =
if field in [:id, :serial_number] do
dynamic(
[s],
ilike(fragment("CAST(? AS text)", field(s, ^field)), ^"%#{search_term}%")
)
else
dynamic([s], ilike(field(s, ^field), ^"%#{search_term}%"))
end
if dynamic_acc do
dynamic([s], ^dynamic_acc or ^condition)
else
condition
end
end)
query |> where(^or_conditions)
end
Now I have a join between 3 tables and ideally I want to pass to the search fields by specifiyng in which table and which columns I want to search on like that for example:
search_fields = [{:device, :name}, {:device_alarm, :level}]
defp joined_table_search(query, _schema, search_term, search_fields) do
binding_names = Enum.map(search_fields, fn {binding_name, _field_atom} -> binding_name end)
IO.inspect(binding_names, label: "BINDING NAMES")
or_conditions =
Enum.reduce(search_fields, nil, fn {binding_name, field_atom}, dynamic_acc ->
IO.inspect(binding_name, label: "BINDING NAME")
IO.inspect(field_atom, label: "FIELD ATOM")
condition =
if field_atom in [:id, :serial_number] do
dynamic(
[binding_names],
ilike(
fragment("CAST(? AS text)", field(^binding_name, ^field_atom)),
^"%#{search_term}%"
)
)
else
dynamic(
[binding_names],
ilike(field(^binding_name, ^field_atom), ^"%#{search_term}%") #Error on this line
)
end
if dynamic_acc do
dynamic([], ^dynamic_acc or ^condition)
else
condition
end
end)
query
|> where(^or_conditions)
end
but this gives me a compilation error on the first else condition about the field atom:
** (Protocol.UndefinedError) protocol String.Chars not implemented for {:^, [line: 186, column: 42], [{:field_atom, [line: 186, column: 43], nil}]} of type Tuple
(elixir 1.16.0) lib/string/chars.ex:3: String.Chars.impl_for!/1
(elixir 1.16.0) lib/string/chars.ex:22: String.Chars.to_string/1
(ecto 3.11.2) lib/ecto/query/builder.ex:642: Ecto.Query.Builder.escape_field!/3
(ecto 3.11.2) lib/ecto/query/builder.ex:88: Ecto.Query.Builder.escape/5
(elixir 1.16.0) lib/enum.ex:1826: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
(ecto 3.11.2) lib/ecto/query/builder.ex:618: Ecto.Query.Builder.escape_call/5
(ecto 3.11.2) lib/ecto/query/builder/dynamic.ex:15: Ecto.Query.Builder.Dynamic.build/3
(ecto 3.11.2) expanding macro: Ecto.Query.dynamic/2
Which is really strange as this should’t be a tuple. I removed that part of the code just to be able to compile and inspect the values and I get
BINDING NAMES: [:device, :device_alarm]
BINDING NAME: :device
FIELD ATOM: :name
BINDING NAME: :device_alarm
FIELD ATOM: :level
What am I doing wrong or is this a bug?
Bug in a sense, that the error message could be friendlier. It would be great if you report this to issues on GitHub.
