I’m not sure I understand the issue you seem to think there is.
As it is, the IN comparison is expecting a list of scalars - (b0."id",b0."uid") would be a list, not a scalar.
But even if it did work, there is another problem:
Each index in the Array of values must correspond to the same index in the Array of keys.
i.e. the query will not necessarily return the records in the same order as the criteria appear in the list - you have to specifically arrange for that:
defp query_album(index, {artist_id, title}) do
seq = Integer.to_string(index)
from(a in Album,
where: a.artist_id == ^artist_id,
where: a.title == ^title,
select: %{seq: ^seq, id: a.id, artist_id: a.artist_id, title: a.title}
)
end
defp query_another(args, {index, query}),
do: {index + 1, union_all(query_album(index, args), ^query)}
defp query_all([head | tail]) do
{_, query} = List.foldl(tail, {1, query_album(0, head)}, &query_another/2)
order_by(query, fragment("1 ASC"))
end
def play do
[
{2, "Portrait In Jazz"},
{3, "Live At Montreaux"},
{1, "Kind Of Blue"}
]
|> query_all()
|> Repo.all()
|> Enum.map(&Map.drop(&1, [:seq]))
end
$ mix run ./priv/repo/playground.exs
asn1: 5.0.9, compiler: 7.4.7, connection: 1.0.4, crypto: 4.6.1, db_connection: 2.0.5, decimal: 1.6.0, ecto: 3.0.6, ecto_sql: 3.0.5, elixir: 1.9.2, hex: 0.20.1, inets: 7.1.1, jason: 1.1.2, kernel: 6.5, logger: 1.9.2, mariaex: 0.9.1, mix: 1.9.2, music_db: 0.1.0, postgrex: 0.14.1, public_key: 1.7, ssl: 9.4, stdlib: 3.10, telemetry: 0.3.0
19:34:58.456 [debug] QUERY OK source="albums" db=0.6ms decode=0.9ms queue=0.7ms
SELECT $1, a0."id", a0."artist_id", a0."title"
FROM "albums" AS a0
WHERE (a0."artist_id" = $2) AND (a0."title" = $3)
UNION ALL (
SELECT $4, a0."id", a0."artist_id", a0."title" FROM "albums" AS a0 WHERE (a0."artist_id" = $5) AND (a0."title" = $6)
UNION ALL (
SELECT $7, a0."id", a0."artist_id", a0."title"
FROM "albums" AS a0
WHERE (a0."artist_id" = $8) AND (a0."title" = $9)
))
ORDER BY 1 ASC ["2", 1, "Kind Of Blue", "1", 3, "Live At Montreaux", "0", 2, "Portrait In Jazz"]
[
%{artist_id: 2, id: 4, title: "Portrait In Jazz"},
%{artist_id: 3, id: 5, title: "Live At Montreaux"},
%{artist_id: 1, id: 1, title: "Kind Of Blue"}
]
The IN comparison can be emulated in this manner:
defp query_album({artist_id, title}) do
from(a in Album,
where: a.artist_id == ^artist_id and a.title == ^title
)
end
defp or_another({artist_id, title}, query),
do: or_where(query, [a], a.artist_id == ^artist_id and a.title == ^title)
defp query_all([head | tail]),
do: List.foldl(tail, query_album(head), &or_another/2)
def play do
[
{2, "Portrait In Jazz"},
{3, "Live At Montreaux"},
{1, "Kind Of Blue"}
]
|> query_all()
|> Repo.all()
end
$ mix run ./priv/repo/playground.exs
asn1: 5.0.9, compiler: 7.4.7, connection: 1.0.4, crypto: 4.6.1, db_connection: 2.0.5, decimal: 1.6.0, ecto: 3.0.6, ecto_sql: 3.0.5, elixir: 1.9.2, hex: 0.20.1, inets: 7.1.1, jason: 1.1.2, kernel: 6.5, logger: 1.9.2, mariaex: 0.9.1, mix: 1.9.2, music_db: 0.1.0, postgrex: 0.14.1, public_key: 1.7, ssl: 9.4, stdlib: 3.10, telemetry: 0.3.0
20:59:53.957 [debug] QUERY OK source="albums" db=5.3ms decode=0.8ms queue=0.7ms
SELECT a0."id", a0."title", a0."inserted_at", a0."updated_at", a0."artist_id"
FROM "albums" AS a0
WHERE (((a0."artist_id" = $1) AND (a0."title" = $2)))
OR ((a0."artist_id" = $3) AND (a0."title" = $4))
OR ((a0."artist_id" = $5) AND (a0."title" = $6)) [2, "Portrait In Jazz", 3, "Live At Montreaux", 1, "Kind Of Blue"]
[
%MusicDB.Album{
__meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
artist: #Ecto.Association.NotLoaded<association :artist is not loaded>,
artist_id: 1,
genres: #Ecto.Association.NotLoaded<association :genres is not loaded>,
id: 1,
inserted_at: ~N[2019-08-03 14:35:27],
title: "Kind Of Blue",
tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
updated_at: ~N[2019-08-03 14:35:27]
},
%MusicDB.Album{
__meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
artist: #Ecto.Association.NotLoaded<association :artist is not loaded>,
artist_id: 2,
genres: #Ecto.Association.NotLoaded<association :genres is not loaded>,
id: 4,
inserted_at: ~N[2019-08-03 14:35:27],
title: "Portrait In Jazz",
tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
updated_at: ~N[2019-08-03 14:35:27]
},
%MusicDB.Album{
__meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
artist: #Ecto.Association.NotLoaded<association :artist is not loaded>,
artist_id: 3,
genres: #Ecto.Association.NotLoaded<association :genres is not loaded>,
id: 5,
inserted_at: ~N[2019-08-03 14:35:27],
title: "Live At Montreaux",
tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
updated_at: ~N[2019-08-03 14:35:27]
}
]
but the order of the records isn’t guaranteed or easily influenced.
I’ll remember that for later.
