What am I missing here?
According to the documentation:
Trying to use that in my sample:
# file: music_db/priv/repo/playground.exs
#
# http://www.pragmaticprogrammer.com/titles/wmecto
# https://pragprog.com/titles/wmecto/source_code
# http://media.pragprog.com/titles/wmecto/code/wmecto-code.zip
#
# pg_ctl -D /usr/local/var/postgres start
# mix format ./priv/repo/playground.exs
# mix run ./priv/repo/playground.exs
#
defmodule AppInfo do
def string() do
Application.loaded_applications()
|> Enum.map(&to_app_keyword/1)
|> Enum.sort_by(&map_app_name/1)
|> Enum.map_join(", ", &app_keyword_to_string/1)
end
defp to_app_keyword({app, _, vsn}),
do: {app, vsn}
defp app_keyword_to_string({app, vsn}),
do: Atom.to_string(app) <> ": " <> to_string(vsn)
defp map_app_name({app, _}),
do: app
end
defmodule Playground do
import Ecto.Query
alias MusicDB.Repo
alias MusicDB.{Album, Track}
def play do
IO.puts(AppInfo.string())
other_query = make_query("Kind Of Blue")
"Live At Montreaux"
|> make_query()
|> union_all(^other_query)
|> order_by([t], asc: t.album_id, asc: t.index)
|> Repo.all()
end
defp make_query(title) do
from(a in Album,
inner_join: t in Track,
on: t.album_id == a.id,
where: a.title == ^title,
select: t
)
end
end
IO.inspect(Playground.play())
Which results in:
$ mix run ./priv/repo/playground.exs
asn1: 5.0.6, compiler: 7.2.3, connection: 1.0.4, crypto: 4.3.2, db_connection: 2.0.1, decimal: 1.5.0, ecto: 3.0.1, ecto_sql: 3.0.0, elixir: 1.7.3, hex: 0.18.1, inets: 7.0.1, kernel: 6.0.1, logger: 1.7.3, mix: 1.7.3, music_db: 0.1.0, poison: 3.1.0, postgrex: 0.14.0, public_key: 1.6.1, ssl: 9.0.1, stdlib: 3.5.1, telemetry: 0.2.0
13:11:09.256 [debug] QUERY ERROR source="albums" db=0.5ms queue=3.4ms
SELECT
t1."id",
t1."title",
t1."duration",
t1."index",
t1."number_of_plays",
t1."inserted_at",
t1."updated_at",
t1."album_id"
FROM "albums" AS a0
INNER JOIN "tracks" AS t1 ON t1."album_id" = a0."id"
WHERE (a0."title" = $1)
UNION ALL (
SELECT
t1."id",
t1."title",
t1."duration",
t1."index",
t1."number_of_plays",
t1."inserted_at",
t1."updated_at",
t1."album_id"
FROM "albums" AS a0
INNER JOIN "tracks" AS t1 ON t1."album_id" = a0."id"
WHERE (a0."title" = $2)
) ORDER BY a0."album_id", a0."index" ["Live At Montreaux", "Kind Of Blue"]
** (Postgrex.Error) ERROR 42P01 (undefined_table) missing FROM-clause entry for table "a0"
query: SELECT t1."id", t1."title", t1."duration", t1."index", t1."number_of_plays", t1."inserted_at", t1."updated_at", t1."album_id" FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON t1."album_id" = a0."id" WHERE (a0."title" = $1) UNION ALL (SELECT t1."id", t1."title", t1."duration", t1."index", t1."number_of_plays", t1."inserted_at", t1."updated_at", t1."album_id" FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON t1."album_id" = a0."id" WHERE (a0."title" = $2)) ORDER BY a0."album_id", a0."index"
(ecto_sql) lib/ecto/adapters/sql.ex:590: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql) lib/ecto/adapters/sql.ex:523: Ecto.Adapters.SQL.execute/5
(ecto) lib/ecto/repo/queryable.ex:147: Ecto.Repo.Queryable.execute/4
(ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
priv/repo/playground.exs:58: (file)
$
Now manually this could be fixed by either going with column position:
SELECT
t1."id",
t1."title",
t1."duration",
t1."index",
t1."number_of_plays",
t1."inserted_at",
t1."updated_at",
t1."album_id"
FROM "albums" AS a0
INNER JOIN "tracks" AS t1 ON t1."album_id" = a0."id"
WHERE (a0."title" = 'Live At Montreaux')
UNION ALL (
SELECT
t1."id",
t1."title",
t1."duration",
t1."index",
t1."number_of_plays",
t1."inserted_at",
t1."updated_at",
t1."album_id"
FROM "albums" AS a0
INNER JOIN "tracks" AS t1 ON t1."album_id" = a0."id"
WHERE (a0."title" = 'Kind Of Blue')
) ORDER BY 8, 4;
or column name without table prefix:
SELECT
t1."id",
t1."title",
t1."duration",
t1."index",
t1."number_of_plays",
t1."inserted_at",
t1."updated_at",
t1."album_id"
FROM "albums" AS a0
INNER JOIN "tracks" AS t1 ON t1."album_id" = a0."id"
WHERE (a0."title" = 'Live At Montreaux')
UNION ALL (
SELECT
t1."id",
t1."title",
t1."duration",
t1."index",
t1."number_of_plays",
t1."inserted_at",
t1."updated_at",
t1."album_id"
FROM "albums" AS a0
INNER JOIN "tracks" AS t1 ON t1."album_id" = a0."id"
WHERE (a0."title" = 'Kind Of Blue')
) ORDER BY "album_id", "index";
Now this particular example uses
SELECT city FROM suppliers UNION SELECT city FROM customers LIMIT 10
but LIMIT doesn’t require any backward reference to the columns like ORDER BY does.
And if you are wondering, this doesn’t work either:
defmodule Playground do
import Ecto.Query
alias MusicDB.Repo
alias MusicDB.{Track}
def play do
IO.puts(AppInfo.string())
album_id = 1
other_query =
from(t in Track,
where: t.album_id == ^album_id
)
album_id = 5
from(t in Track,
where: t.album_id == ^album_id,
union_all: ^other_query,
order_by: [:album_id, :index]
)
|> Repo.all()
end
end
$ mix run ./priv/repo/playground.exs
asn1: 5.0.6, compiler: 7.2.3, connection: 1.0.4, crypto: 4.3.2, db_connection: 2.0.1, decimal: 1.5.0, ecto: 3.0.1, ecto_sql: 3.0.0, elixir: 1.7.3, hex: 0.18.1, inets: 7.0.1, kernel: 6.0.1, logger: 1.7.3, mix: 1.7.3, music_db: 0.1.0, poison: 3.1.0, postgrex: 0.14.0, public_key: 1.6.1, ssl: 9.0.1, stdlib: 3.5.1, telemetry: 0.2.0
14:05:41.819 [debug] QUERY ERROR source="tracks" db=0.6ms queue=2.9ms
SELECT
t0."id",
t0."title",
t0."duration",
t0."index",
t0."number_of_plays",
t0."inserted_at",
t0."updated_at",
t0."album_id"
FROM "tracks" AS t0
WHERE (t0."album_id" = $1)
UNION ALL (
SELECT
t0."id",
t0."title",
t0."duration",
t0."index",
t0."number_of_plays",
t0."inserted_at",
t0."updated_at",
t0."album_id"
FROM "tracks" AS t0
WHERE (t0."album_id" = $2)
) ORDER BY t0."album_id", t0."index" [5, 1]
** (Postgrex.Error) ERROR 42P01 (undefined_table) missing FROM-clause entry for table "t0"
query: SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE (t0."album_id" = $1) UNION ALL (SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 WHERE (t0."album_id" = $2)) ORDER BY t0."album_id", t0."index"
(ecto_sql) lib/ecto/adapters/sql.ex:590: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql) lib/ecto/adapters/sql.ex:523: Ecto.Adapters.SQL.execute/5
(ecto) lib/ecto/repo/queryable.ex:147: Ecto.Repo.Queryable.execute/4
(ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
priv/repo/playground.exs:55: (file)
$
PostgreSQL 10.5
