Yeah, unfortunately then I think you will have to hard code the table name. If you have multiple table names, you will need a case and a clause for each:

  def thing(schema) do
    other_table = Other.Schema.__schema__(:source)
    from(a in My.Schema) |> add_exists(other_table) |> Repo.all()
  end

  defp add_exists(query, "omg")
    from q in query,
      where: fragment("exists (
        SELECT 1
        FROM omg o
        WHERE o.column_name = ?)", ^q.my_field)
      )
  end

Sorry.

Hi @Frogglet! We don’t allow interpolation because it can be unsafe and lead to SQL injection attacks.

Have you tried passing the table name as fragment interpolation: fragment(“... FROM ? ....”, ^table_name)?

I had a similar issue trying to dynamically build fragment strings (from a trusted source) and it seems like a general escape hatch for these kind of issues, when trying to use third party macros with dynamic inputs where not explicitly supported is to use Code.eval_quoted. So, for example, you can wrap an entire select statement and fool Elixir/Ecto into thinking the variables are in fact literals:

Code.eval_quoted(
      quote do
        select(unquote(escaped_q), [table], %{
          dynamic_select: fragment(unquote(some_var), table.field))
        })
      end
    )

I am pretty positive this is the last tool you should reach for and maybe it would be better to use postgrex directly for these cases. I haven’t tried that so I can’t speak to the pros/cons but I’d love to hear people’s thoughts about the practice, and maybe whether Ecto could adopt some “blessed” way of circumventing protections like this one because I certainly feel dirty doing this.

Are you by chance using PostgreSQL? If so then SELECT 1 FROM table WHERE pattern LIMIT 1 will be equally performant. Unfortunately there is no way to do string interpolation in fragment as IIRC it is compile-time checked for correctness. There was fragment_unsafe for brief moment but IIRC it was removed.

@taylordowns2000
working example:

def search_text(query, %{"search_string" => search_string}) do
  from(r in query,
    where:
      fragment(
        "jsonb_path_exists(?, '$.** \\? (@ == $search_string)', jsonb_build_object('search_string', ?::text))",
        r.body,
        ^search_string
      )
  )
end