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

Questions / Help
1

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.

2 Likes
2

Hi
Your problematic seems a lot more complicated than mine.
And I already read a lot of posts and used library you wrote, so I doubt any ideas of mine would bring something you didn’t think of already.

Yet I can share my case in any eventuality:

I wanted to perform a “partial search” on integers (=~ or ilike), to let a user search through postal codes (for eg) without having him to completely write the full correct one (and in bonus let him sub-select a family).
For a reason beyond my understanding it is not natively possible in Postgres, so I had to convert/cast the field into a string.
(I hoped @field_source_mapper could help me creating a populated virtual field and casting it, but … no)
I didn’t want to create a custom type, it felt too heavy, and I didn’t want my data to be persisted either.

Long story short, here was my first attempt:

TargetSchema
      |> select(
        [target_table],
        merge(target_table, 
          %{target_field_as_string: 
               fragment("(?)::varchar(255)", target_table.target_field)
               })
        )
        |> join(...)
        |> preload(...)
        ...

I there I stumbled upon the same problem: this code is invoked by some magic (Flop and so on), and subqueries are added at runtime (where clauses, etc), with a logically awaited binding: target_table.target_field_as_string … and … booom

So here was my very simplistic hack:
move the select/merge operation into a subquery (nothing more really), and voilà!
It allows (or trick ?) Ecto to rebind all sub-variables, and all the magic can come back.

subq = 
TargetSchema
      |> select(
        [target_table],
        merge(target_table, 
          %{target_field_as_string: 
               fragment("(?)::varchar(255)", target_table.target_field)
               })
        )

        subquery(subq)
        |> join(...)
        |> preload(...)
        ...

I know it’s been almost two years since your post, happy if it can help someone.

Nota: I added target_field_as_string as a virtual field (string type, obviously) in my TargetSchema.

P.S. I tried naively:
1- fragment("(?)::varchar(255) as target_field_as_string", target_table.target_field) => it didn’t work obviously
2- fragment("(?)::varchar(255)", target_table.target_field, field(target_table, :target_field_as_string)) => crashed as field `target_field_as_string` in `select` is a virtual field ... (but I had to keep my virtual field for Flop composition to come, at user interaction time)