Custom column names in fragment

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.

Here is an example:

{:ok, datetime} = DateEx.convert_date_to_datetime(date)

CheapestConnection
|> where([cc], cc.kind == "round_trip_recent")
|> order_by([cc], fragment("abs(date_part('day', departure_date - ?)), price", type(^datetime, :utc_datetime)))    
|> CheapestConnectionsRepo.all()

The column I would like to choose depending on certain conditions (like DB/Repo is used) is departure_date. That column name is different in another database. As a side note this query basically orders records by closest date relative to datetime.

Would:

fragment("abs(date_part('day', departure_date - ?)) as day, price"

work?

@kip Thanks for the suggestion, actually I am looking for something like:

CheapestConnection
|> where([cc], cc.kind == "round_trip_recent")
|> order_by([cc], fragment("abs(date_part('day', ? - ?)), price", custom_column_name, type(^datetime, :utc_datetime)))    
|> CheapestConnectionsRepo.all()

As I mentioned before custom_column_name will be wrapped into double-quotes which is not desired.

You can solve this by defining the same field with different sources in schemas

defmodule Schema1 do
  schema "table1" do
    field(:custom_column_name, :utc_datetime, source: :column_name1
  end
end
defmodule Schema2 do
  schema "table2" do
    field(:custom_column_name, :utc_datetime, source: :column_name2
  end
end

schema_module
|> where([cc], cc.kind == "round_trip_recent")
|> order_by([cc], fragment("abs(date_part('day', ? - ?)), price", cс.custom_column_name, type(^datetime, :utc_datetime)))    
|> CheapestConnectionsRepo.all()
2 Likes

@fuelen Thanks a lot! Yes, that solves the problem.