Ecto: Allow table name in fragments

[yrashk]

I have some fairly complex queries, and I often have to resort to queries. For simple ones, having something like fragment("column = ?", ^value) is sufficient, however, once the joins get into the picture, there is always a chance for column ambiguity (especially if you're joining with the same table). Ecto generates queries with table aliases like f0. Is there any way we can get this in fragments?

[troyk]

I wish ecto would support something similar to ActiveRecords find_by_sql

[josevalim]

It already supports both:

1. fragment("? = ?", p.column, ^value)
2. Ecto.Adapters.SQL.query(repo, "ANY SQL YOU WANT", [])

[josevalim]

The second one is available only on master.

[cmititiuc]

For others trying to access jsonb data, you can do

fragment("?->>'json_key' = ?", a.column_name, ^some_value)

results in a0."column_name"->>'json_key' = ($1) in your query

where a0 is the table name that gets dynamically assigned. I was having trouble with ambiguous_column errors and couldn't figure out how to include the table name in the fragment.

[lkananowicz]

Is there a way to use custom/varying column names in fragment? I mean the situation where I have 2 almost similar tables coming from different databases. Only couple of columns are different for historical reasons and I use those column names inside fragment(s). I want to unify Ecto queries to both of those tables as they were executed separately before.

Using simple questionmark notation fragment("?", column_name) causes column name to be appended into fragment however column ends up wrapped into double-quotes which is not desired.

[OvermindDL1]

causes column name to be appended into fragment however column ends up wrapped into double-quotes which is not desired.

Why not? That's what column names should be overall?

Also, should ask this on the Elixir forums, it won't really get visibility in a dead issue. :-)

[lkananowicz]

@OvermindDL1 Thanks for the tip. I am going to ask on Elixir forums.