Ecto 3: How to use order_by with union_all?

ecto

#1

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


#2

Workaround - stuff the union into a subquery:

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")
    query = make_query("Live At Montreaux")
    union_query = union_all(query, ^other_query)
    ordered_query =
      from(u in subquery(union_query),
        order_by: [asc: u.album_id, asc: u.index]
      )

    Repo.all(ordered_query)
  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
$ 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

15:06:30.728 [debug] QUERY OK db=5.6ms decode=0.6ms queue=1.8ms

SELECT
  s0."id",
  s0."title",
  s0."duration",
  s0."index",
  s0."number_of_plays",
  s0."inserted_at",
  s0."updated_at",
  s0."album_id"
FROM (
    SELECT
      t1."id" AS "id",
      t1."title" AS "title",
      t1."duration" AS "duration",
      t1."index" AS "index",
      t1."number_of_plays" AS "number_of_plays",
      t1."inserted_at" AS "inserted_at",
      t1."updated_at" AS "updated_at",
      t1."album_id" AS "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))
) AS s0
ORDER BY s0."album_id", s0."index" ["Live At Montreaux", "Kind Of Blue"]

[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 544,
    duration_string: nil,
    id: 1,
    index: 1,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "So What",
    updated_at: ~N[2018-11-06 13:40:37]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 574,
    duration_string: nil,
    id: 2,
    index: 2,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "Freddie Freeloader",
    updated_at: ~N[2018-11-06 13:40:37]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 327,
    duration_string: nil,
    id: 3,
    index: 3,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "Blue In Green",
    updated_at: ~N[2018-11-06 13:40:37]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 693,
    duration_string: nil,
    id: 4,
    index: 4,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "All Blues",
    updated_at: ~N[2018-11-06 13:40:37]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 481,
    duration_string: nil,
    id: 5,
    index: 5,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "Flamenco Sketches",
    updated_at: ~N[2018-11-06 13:40:37]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 5,
    duration: 761,
    duration_string: nil,
    id: 30,
    index: 1,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "Anton's Ball",
    updated_at: ~N[2018-11-06 13:40:37]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 5,
    duration: 647,
    duration_string: nil,
    id: 31,
    index: 2,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "The Moontrane",
    updated_at: ~N[2018-11-06 13:40:37]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 5,
    duration: 805,
    duration_string: nil,
    id: 32,
    index: 3,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "Farallone",
    updated_at: ~N[2018-11-06 13:40:37]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 5,
    duration: 844,
    duration_string: nil,
    id: 33,
    index: 4,
    inserted_at: ~N[2018-11-06 13:40:37],
    number_of_plays: 0,
    title: "Song Of Songs",
    updated_at: ~N[2018-11-06 13:40:37]
  }
]
$ 

As subqueries don’t allow preloads, a fragment is required instead:

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Album}

  def play do
    IO.puts(AppInfo.string())

    other_query = make_query("Kind Of Blue")
    query = make_query("Live At Montreaux")

    union_query =
      query
      |> union_all(^other_query)
      |> order_by([a,t], fragment("\"album_id\", \"index\""))

    Repo.all(union_query)
  end

  defp make_query(title) do
    from(a in Album,
      inner_join: t in assoc(a, :tracks),
      where: a.title == ^title,
      preload: [tracks: t]
    )
  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

18:04:28.668 [debug] QUERY OK source="albums" db=5.3ms decode=0.5ms queue=1.0ms

  SELECT
    a0."id",
    a0."title",
    a0."inserted_at",
    a0."updated_at",
    a0."artist_id",
    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
    a0."id",
    a0."title",
    a0."inserted_at",
    a0."updated_at",
    a0."artist_id",
    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 "album_id", "index" ["Live At Montreaux", "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[2018-11-06 13:40:37],
    title: "Kind Of Blue",
    tracks: [
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 1,
        duration: 544,
        duration_string: nil,
        id: 1,
        index: 1,
        inserted_at: ~N[2018-11-06 13:40:37],
        number_of_plays: 0,
        title: "So What",
        updated_at: ~N[2018-11-06 13:40:37]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 1,
        duration: 574,
        duration_string: nil,
        id: 2,
        index: 2,
        inserted_at: ~N[2018-11-06 13:40:37],
        number_of_plays: 0,
        title: "Freddie Freeloader",
        updated_at: ~N[2018-11-06 13:40:37]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 1,
        duration: 327,
        duration_string: nil,
        id: 3,
        index: 3,
        inserted_at: ~N[2018-11-06 13:40:37],
        number_of_plays: 0,
        title: "Blue In Green",
        updated_at: ~N[2018-11-06 13:40:37]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 1,
        duration: 693,
        duration_string: nil,
        id: 4,
        index: 4,
        inserted_at: ~N[2018-11-06 13:40:37],
        number_of_plays: 0,
        title: "All Blues",
        updated_at: ~N[2018-11-06 13:40:37]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 1,
        duration: 481,
        duration_string: nil,
        id: 5,
        index: 5,
        inserted_at: ~N[2018-11-06 13:40:37],
        number_of_plays: 0,
        title: "Flamenco Sketches",
        updated_at: ~N[2018-11-06 13:40:37]
      }
    ],
    updated_at: ~N[2018-11-06 13:40:37]
  },
  %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[2018-11-06 13:40:37],
    title: "Live At Montreaux",
    tracks: [
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 5,
        duration: 761,
        duration_string: nil,
        id: 30,
        index: 1,
        inserted_at: ~N[2018-11-06 13:40:37],
        number_of_plays: 0,
        title: "Anton's Ball",
        updated_at: ~N[2018-11-06 13:40:37]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 5,
        duration: 647,
        duration_string: nil,
        id: 31,
        index: 2,
        inserted_at: ~N[2018-11-06 13:40:37],
        number_of_plays: 0,
        title: "The Moontrane",
        updated_at: ~N[2018-11-06 13:40:37]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 5,
        duration: 805,
        duration_string: nil,
        id: 32,
        index: 3,
        inserted_at: ~N[2018-11-06 13:40:37],
        number_of_plays: 0,
        title: "Farallone",
        updated_at: ~N[2018-11-06 13:40:37]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 5,
        duration: 844,
        duration_string: nil,
        id: 33,
        index: 4,
        inserted_at: ~N[2018-11-06 13:40:37],
        number_of_plays: 0,
        title: "Song Of Songs",
        updated_at: ~N[2018-11-06 13:40:37]
      }
    ],
    updated_at: ~N[2018-11-06 13:40:37]
  }
]
$