Let’s say that you want to interpolate a value into your query so that you can change the key returned. (Clearly this is a simplified example, but you could imagine a combination of some operation and column being used in a framework to build queries I hope). You might think of pinning an atom in since that’s what you would normally use:
import Ecto.Query
x = :play_id
q = from p in Galaxy.Dimension.Play, select: %{}, select_merge: %{^x => p.id}, limit: 10
Galaxy.Repo.all(q)
[debug] QUERY ERROR source="dm_plays" db=9.0ms queue=1.6ms
SELECT $1, d0."id" FROM "dm_plays" AS d0 LIMIT 10 [:play_id]
** (DBConnection.EncodeError) Postgrex expected a binary, got :play_id. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
(postgrex) lib/postgrex/type_module.ex:897: Postgrex.DefaultTypes.encode_params/3
(postgrex) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
(db_connection) lib/db_connection.ex:1148: DBConnection.encode/5
(db_connection) lib/db_connection.ex:1246: DBConnection.run_prepare_execute/5
(db_connection) lib/db_connection.ex:1342: DBConnection.run/6
(db_connection) lib/db_connection.ex:539: DBConnection.parsed_prepare_execute/5
(db_connection) lib/db_connection.ex:532: DBConnection.prepare_execute/4
(ecto_sql) lib/ecto/adapters/sql.ex:570: Ecto.Adapters.SQL.execute!/4
(ecto_sql) lib/ecto/adapters/sql.ex:562: Ecto.Adapters.SQL.execute/5
(ecto) lib/ecto/repo/queryable.ex:177: Ecto.Repo.Queryable.execute/4
(ecto) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
That didn’t work, so clearly there’s a problem and it tells you it expected a binary, so you might try that and find out it does work:
x = "play_id"
q = from p in Galaxy.Dimension.Play, select: %{}, select_merge: %{^x => p.id}, limit: 10
Galaxy.Repo.all(q)
[debug] QUERY ERROR source="dm_plays" db=9.0ms queue=1.6ms
SELECT $1, d0."id" FROM "dm_plays" AS d0 LIMIT 10 ["play_id"]
[
%{"play_id" => 1250851},
%{"play_id" => 2336005},
%{"play_id" => 2456386},
%{"play_id" => 2457171},
%{"play_id" => 215796},
%{"play_id" => 2334493},
%{"play_id" => 2335244},
%{"play_id" => 215802},
%{"play_id" => 2505777},
%{"play_id" => 2334997}
]
So I notice that it includes the “key” as part of the query when it’s interpolated, so I wonder a bit about how that works and I try the following and notice it literally includes the value as a literal in the query:
q = from p in Galaxy.Dimension.Play, select: %{}, select_merge: %{^"play_id" => p.id, ^"type" => p.type}, limit: 10
q |> Galaxy.Repo.all()
[debug] QUERY OK source="dm_plays" db=0.9ms queue=1.7ms
SELECT $1, d0."id", $2, d0."type" FROM "dm_plays" AS d0 LIMIT 10 ["play_id", "type"]
[
%{"play_id" => 1250851, "type" => "R"},
%{"play_id" => 2336005, "type" => "R"},
%{"play_id" => 2456386, "type" => "R"},
%{"play_id" => 2457171, "type" => "R"},
%{"play_id" => 215796, "type" => "R"},
%{"play_id" => 2334493, "type" => "R"},
%{"play_id" => 2335244, "type" => "R"},
%{"play_id" => 215802, "type" => "R"},
%{"play_id" => 2505777, "type" => "R"},
%{"play_id" => 2334997, "type" => "R"}
]
OK, that’s pretty weird, but I can live with it. But there are cases where that same query might need to be included in a different context like a subquery, and so you try to do that and find out that it doesn’t work at all, and the only thing you can use is a compile time atom:
x = "play_id"
q = from p in Galaxy.Dimension.Play, select: %{}, select_merge: %{^x => p.id}, limit: 10
Galaxy.Repo.all(from x in subquery(q))
** (Ecto.SubQueryError) the following exception happened when compiling a subquery.
** (Ecto.QueryError) only atom keys are allowed when selecting a map in subquery, got: `%{^0 => &0.id()}` in query:
from p0 in Galaxy.Dimension.Play,
limit: 10,
select: %{^"play_id" => p0.id}
The subquery originated from the following query:
from p0 in subquery(from p0 in Galaxy.Dimension.Play,
limit: 10,
select: %{^"play_id" => p0.id}),
select: p0
(ecto) lib/ecto/repo/queryable.ex:161: Ecto.Repo.Queryable.execute/4
(ecto) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
Now we’ve come full circle. In an outer query you can interpolate a string, but in a subquery it has to be an atom, and since you can’t interpolate an atom you can’t interpolate into subquries.
Is this correct? This all seems fairly counter-intuitive to me.
I understand that I could use macros to write these, but then you’re talking about some macro expansion per custom key (and multiply that times the number of operations you support on that key). I had been using a Protocol with __deriving__
but that causes a HUGE increase in compilation time as you create hundreds of modules at compile time so I was trying to find a simpler way.
Any ideas?