How to create the right Ecto query for the given SQL query

Questions / Help
1

Hello,

i am builing a POC and i run in a little problem.

I created a schema with 3 virtual fields, which a i want to fill with select_merge
in an ecto query.

To know, that everthing works fine, i first created the sql:

SELECT p0."id", p0."date", p0."slots", p0."real_slots", p0."inserted_at", p0."updated_at", 
count(b1."id"), count(d2."id"), count(d3."id") FROM "planners" AS p0 
LEFT OUTER JOIN "bookings" AS b1 ON b1."planner_id" = p0."id" 
**LEFT OUTER JOIN "planners_dishes" AS p4 ON p4."planner_id" = p0."id"** 
LEFT OUTER JOIN "dishes" AS d2 ON (p4."dish_id" = d2."id") AND (d2."type" = 'meat') 
LEFT OUTER JOIN "dishes" AS d3 ON (p4."dish_id" = d3."id") AND (d3."type" = 'fish') 
GROUP BY p0."id" ORDER BY p0."date"

As you can see, i have two table “planner” and “dishes”, which are in relation and
a table “planners_dishes” in which i store the many-to-many relations.

Now my problem:

When i try to create an ecto query for the sql query above, i always end in this query :

SELECT p0."id", p0."date", p0."slots", p0."real_slots", p0."inserted_at", p0."updated_at", 
count(b1."id"), count(d2."id"), count(d3."id") FROM "planners" AS p0 
LEFT OUTER JOIN "bookings" AS b1 ON b1."planner_id" = p0."id" 
**LEFT OUTER JOIN "planners_dishes" AS p4 ON p4."planner_id" = p0."id"** 
LEFT OUTER JOIN "dishes" AS d2 ON (p4."dish_id" = d2."id") AND (d2."type" = 'meat') 
**LEFT OUTER JOIN "planners_dishes" AS p5 ON p5."planner_id" = p0."id"** 
LEFT OUTER JOIN "dishes" AS d3 ON (p5."dish_id" = d3."id") AND (d3."type" = 'fish') 
GROUP BY p0."id" ORDER BY p0."date"

The result is, that the counts of the query aer always double.

My ecto query looks like this :

def get_planner() do
    from planner in CanteenBackend.Planning.Planner, group_by: planner.id, order_by: [asc: :date]
end

def actual_slots(query) do
    from planner in query,
      left_join: booking in assoc(planner, :booking),
        select_merge: %{actual_slots: count(booking.id)}
end

def actual_meat(query) do
    from planner in query,
      left_join: dish in assoc(planner, :dishes),
        on: dish.type == "fleischig",
          select_merge: %{actual_meat: count(dish.id)}
end

def actual_fish(query) do
    from planner in query,
      left_join: dish in assoc(planner, :dishes),
        on: dish.type == "fischig",
          select_merge: %{actual_fish: count(dish.id)}
end


get_planner()|>  actual_slots|> actual_meat |> actual_fish |>Repo.all

Please, can anybody help me to fix the ecto query?

Thanks you!

2

Have you tried instead of

left_join: booking in assoc(planner, :booking),

using

left_join: booking in CanteenBackend.Planning.Booking, on: planner.id == booking.planner_id,

etc.

I suspect that by using assoc you are forcing the INNER JOIN because the joining graph has to be continuous.

3

Sorry, I made a mistake in my first post. I use only left outer joins and not inner joins. I always need the list of planners.

4

The generated SQL is a consistent result of how you defined the association in the schema because both parts use the same:

left_join: dish in assoc(planner, :dishes),

… so formulate the query according to the original SQL and ignore the existence of the association. Now whether your associations are defined correctly is difficult to tell without the schema/data model.

5

Based on the information given it seems you have declared a many_to_many association across

planner <-planner_id- planner_dishes -dish_id-> dishes

that is responsible for the “extra” JOIN that you are seeing in the Ecto generated SQL when you use assoc(planner, :dishes) - making it impossible to split the query where you need to.

So either stop using that association and/or reformulate the query.

Example: Starting with a monolithic query:

# 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

# SELECT a.name, COUNT(g_jazz.id), COUNT(g_live.id) FROM artists a
# LEFT OUTER JOIN albums b ON a.id = b.artist_id
# LEFT OUTER JOIN albums_genres ag ON b.id = ag.album_id
# LEFT OUTER JOIN genres g_jazz ON ag.genre_id = g_jazz.id AND g_jazz.name = 'jazz'
# LEFT OUTER JOIN genres g_live ON ag.genre_id = g_live.id AND g_live.name = 'live'
# GROUP BY a.id;

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Artist, Album, AlbumGenre, Genre}

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

    base_query =
      from(a in Artist,
        left_join: b in Album,
        on: a.id == b.artist_id,
        left_join: ag in AlbumGenre,
        on: b.id == ag.album_id,
        left_join: g_jazz in Genre,
        on: ag.genre_id == g_jazz.id and g_jazz.name == "jazz",
        left_join: g_live in Genre,
        on: ag.genre_id == g_live.id and g_live.name == "live",
        group_by: a.id,
        select: %{name: a.name, jazz: count(g_jazz.id), live: count(g_live.id)},
        order_by: [asc: a.name]
      )

    results = Repo.all(base_query)

    results
  end
end

IO.inspect(Playground.play())

It can be broken down into composable parts:

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Artist, Album, AlbumGenre, Genre}

  def base_query do
    from(a in Artist,
      left_join: b in Album,
      on: a.id == b.artist_id,
      left_join: ag in AlbumGenre,
      on: b.id == ag.album_id,
      group_by: a.id,
      select: %{name: a.name},
      order_by: [asc: a.name]
    )
  end

  def count_jazz(query) do
    from([a, b, ag] in query,
      left_join: g_jazz in Genre,
      on: ag.genre_id == g_jazz.id and g_jazz.name == "jazz",
      select_merge: %{jazz: count(g_jazz.id)}
    )
  end

  def count_live(query) do
    from([a, b, ag] in query,
      left_join: g_live in Genre,
      on: ag.genre_id == g_live.id and g_live.name == "live",
      select_merge: %{live: count(g_live.id)}
    )
  end

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

    results =
      base_query()
      |> count_jazz()
      |> count_live()
      |> Repo.all()

    results
  end
end

$ mix run priv/repo/playground.exs
asn1: 5.0.7, compiler: 7.2.6, connection: 1.0.4, crypto: 4.3.3, db_connection: 2.0.1, decimal: 1.5.0, ecto: 3.0.1, ecto_sql: 3.0.0, elixir: 1.7.4, hex: 0.18.2, inets: 7.0.2, kernel: 6.1, logger: 1.7.4, mix: 1.7.4, music_db: 0.1.0, poison: 3.1.0, postgrex: 0.14.0, public_key: 1.6.2, ssl: 9.0.3, stdlib: 3.6, telemetry: 0.2.0

15:33:27.953 [debug] QUERY OK source="artists" db=1.3ms decode=0.7ms queue=1.2ms
SELECT a0."name", count(g3."id"), count(g4."id") FROM "artists" AS a0 LEFT OUTER JOIN "albums" AS a1 ON a0."id" = a1."artist_id" LEFT OUTER JOIN "albums_genres" AS a2 ON a1."id" = a2."album_id" LEFT OUTER JOIN "genres" AS g3 ON (a2."genre_id" = g3."id") AND (g3."name" = 'jazz') LEFT OUTER JOIN "genres" AS g4 ON (a2."genre_id" = g4."id") AND (g4."name" = 'live') GROUP BY a0."id" ORDER BY a0."name" []
[
  %{jazz: 2, live: 0, name: "Bill Evans"},
  %{jazz: 1, live: 1, name: "Bobby Hutcherson"},
  %{jazz: 2, live: 1, name: "Miles Davis"}
]
$

However with respect to associations there can be problems like given the following schemas:

  schema "artists" do
    field(:name)
    field(:birth_date, :date)
    field(:death_date, :date)
    timestamps()

    has_many(:albums, Album)
    has_many(:tracks, through: [:albums, :tracks])
  end

  schema "albums" do
    field(:title, :string)
    timestamps()

    belongs_to(:artist, Artist)
    has_many(:tracks, Track)
    many_to_many(:genres, Genre, join_through: "albums_genres")
  end

  schema "albums_genres" do
    belongs_to(:albums, Album)
    belongs_to(:genres, Genre)
  end

  schema "genres" do
    field(:name)
    field(:wiki_tag)
    timestamps()

    many_to_many(:albums, Album, join_through: "albums_genres")
  end

In this case the many_to_many association is getting in the way of being able to “split” the query in the right place. So in order to use associations the query has to be modified:

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

  def base_query do
    from(a in Artist,
      left_join: b in assoc(a, :albums),
      left_join: g in assoc(b, :genres),
      group_by: a.id,
      select: %{name: a.name},
      order_by: [asc: a.name]
    )
  end

  def count_jazz(query) do
    from([a, b, g] in query,
      select_merge: %{jazz: filter(count(1), g.name == "jazz")}
    )
  end

  def count_live(query) do
    from([a, b, g] in query,
      select_merge: %{live: filter(count(1), g.name == "live")}
    )
  end

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

    results =
      base_query()
      |> count_jazz()
      |> count_live()
      |> Repo.all()

    results
  end
end

$ mix run priv/repo/playground.exs
asn1: 5.0.7, compiler: 7.2.6, connection: 1.0.4, crypto: 4.3.3, db_connection: 2.0.1, decimal: 1.5.0, ecto: 3.0.1, ecto_sql: 3.0.0, elixir: 1.7.4, hex: 0.18.2, inets: 7.0.2, kernel: 6.1, logger: 1.7.4, mix: 1.7.4, music_db: 0.1.0, poison: 3.1.0, postgrex: 0.14.0, public_key: 1.6.2, ssl: 9.0.3, stdlib: 3.6, telemetry: 0.2.0

15:17:31.436 [debug] QUERY OK source="artists" db=1.4ms decode=0.7ms queue=1.9ms
SELECT a0."name", count(1) FILTER (WHERE g2."name" = 'jazz'), count(1) FILTER (WHERE g2."name" = 'live') FROM "artists" AS a0 LEFT OUTER JOIN "albums" AS a1 ON a1."artist_id" = a0."id" LEFT OUTER JOIN "albums_genres" AS a3 ON a3."album_id" = a1."id" LEFT OUTER JOIN "genres" AS g2 ON a3."genre_id" = g2."id" GROUP BY a0."id" ORDER BY a0."name" []
[
  %{jazz: 2, live: 0, name: "Bill Evans"},
  %{jazz: 1, live: 1, name: "Bobby Hutcherson"},
  %{jazz: 2, live: 1, name: "Miles Davis"}
]
$
3 Likes