godzila

godzila

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?

Showing Posts 1 to 2

fuelen

fuelen

I’d say both :smiley: Bug in a sense, that the error message could be friendlier. It would be great if you report this to issues on GitHub.

Ecto.Query.API.field/2 doesn’t accept atom in the first argument.
You should write this instead:

dynamic(
  [{^binding_name, records}],
  ilike(field(records, ^field_atom), ^"%#{search_term}%")
)
godzila

godzila OP

This worked briliantly, thanks! For anyone coming up to this thread remember to assign binding names using as: in your query

— All posts loaded —

Where Next? Top

Trending in Questions Top

katta
I having some trouble figuring out if I have set myself too strict of standards for my production server. Currently I can handle 75% of r...
New
brecabral
Documentation While reading the Scoped Routes section, I noticed that the documentation currently refers to a problem without explainin...
New
achenet
Hello, I’m trying to build a basic Phoenix web-app, and I’d like to use Tailwind. However, when I launch mix phx.server, I get an error...
New
kpanic
Hi everyone, I am toying with the idea of building a “match maker” for giving personal help to people that wants to start coding. I sta...
New
asweet-confluent
I recently noticed that Elixir’s Logger defaults its primary log level to :debug when no :logger, :level application configuration is pre...
New
Cxx-mlr
I’m working on a small exercise involving update_in/3, and I came up with this solution: data = %{ name: "Periodic Table", category:...
New
ChrisAmelia
I’ve got trouble wrapping my head around the order in which functions are called in this snippet (from Phoenix’s authentication): toke...
New

Other Trending Topics Top

GenericJam
Edit: 2026 May 15 - This post is archived. Mob is alive!! Main docs: mob v0.7.11 — Documentation A bit of explanation for the slightly c...
New
JesseHerrick
Hey, I’m Jesse and I’m the main contributor behind Dexter, a full-featured, lightning-fast Elixir LSP optimized for large codebases. It s...
New
mudasobwa
I am happy to introduce the very α version of the new programming language compiled to BEAM. Welcome Cure. It has literally three kille...
New
garrison
Hobbes is a low-level distributed database for the Elixir programming language. Hobbes provides a simple, safe, and scalable storage lay...
New
mhanberg
Hi everyone! The first release candidate for the Expert language server project is now available! We’ve published a press release detai...
New
budgie
A little off-topic, but I feel like people here have a good head on their shoulders. I used to be quite good at making software. Was luc...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews