How to Access Ecto's table alias in a fragment?

I’m trying to access the table alias of an Ecto query inside a fragment. Is this possible? I suspect not because the table alias is probably determined at runtime, not compile time. But I live in hope. Why do I want this?

  • dynamic can’t be used in a SELECT clause
  • dynamic has to be in the root of a WHERE clause making nil checks tricky. No is_nil(dynamic([a], fragment("....")))
  • dynamic is required if parameters are only known at runtime and there is processing to be done on the parameters before interpolation into a fragment.

In this case I have implemented a database function which has some other benefits. Although not a path everyone follows, in this case the implementation heavily leverages Postgres JSONB types so there’s no chance of swapping DB engines on the horizon.

However a database function that accesses the content of the row being processed needs to know the name of the table being used in the query. When a table alias is being used, the name is the alias - not the table name. Consider this example:

SELECT 
  count(a0.\"id\") 
FROM 
  \"articles\" AS a0 
WHERE 
  (NOT (translate_field(a0, $1::varchar, $2::varchar, $3::varchar, $4::varchar[]) IS NULL))

Here we can see that the table alias is a0. (Note - no surrounding quotes). In Elixir this is called as:

from(
  a in Book,
  where: not is_nil(translated(Book, a.title, Factory.locales([:it, :es]))),
  select: count(a.id)
)

translated/3 here is a macro that generates a fragment. To construct the fragment, and the embedded database function call, I need to know the table alias to pass in. Currently I am using a heuristic derived from the ecto binding (a) but thats a bit brittle.

Note too that the table alias has no surrounding quotes meaning fragment interpolation isn’t possible. It has to be Elixir string interpolation - which is another reason this has to be done in a macro. The final database function call looks like:

translate_field(a0, $1::varchar, $2::varchar, $3::varchar, $4::varchar[])

Any ideas would be most welcome.

1 Like