Composing Ecto Query By ID

Questions / Help
3

You should be able to avoid the join entirely, i.e.:

def list_comments_by_author(author_id) do
  Comment
  |> where([c], c.author_id == ^author_id)
  |> Repo.all()
end

Given

  # from music_db/lib/music_db/artist.ex

  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

and

  # from music_db/lib/music_db/album.ex

  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

we have (see Ecto.Schema - Reflection)

$ iex -S mix
iex(1)> Artist.__schema__(:fields)
[:id, :name, :birth_date, :death_date, :inserted_at, :updated_at]
iex(2)> Artist.__schema__(:associations) 
[:albums, :tracks]
iex(3)> Album.__schema__(:fields)       
[:id, :title, :inserted_at, :updated_at, :artist_id]
iex(4)> Album.__schema__(:associations)
[:artist, :tracks, :genres]
iex(5)>

# 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 Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Album}

  def play do
    artist_id = 2
    result = list_albums_by_artist(artist_id)
    IO.puts("list_albums_by_artist: #{inspect result}")
    result = list_albums_by_artist_no_join(artist_id)
    IO.puts("list_albums_by_artist_no_join: #{inspect result}")
  end

  def list_albums_by_artist(artist_id) do
    Album
    |> join(:left, [m], a in assoc(m, :artist))
    |> where([m, a], a.id == ^artist_id)
    |> select([m, _], m)
    |> Repo.all()
  end

  def list_albums_by_artist_no_join(artist_id) do
    Album
    |> where([m], m.artist_id == ^artist_id)
    |> Repo.all()
  end

end

Playground.play()

$ mix run ./priv/repo/playground.exs

10:02:27.403 [debug] QUERY OK source="albums" db=1.8ms decode=1.6ms

SELECT a0."id", a0."title", a0."inserted_at", a0."updated_at", a0."artist_id" FROM "albums" AS a0 LEFT OUTER JOIN "artists" AS a1 ON a1."id" = a0."artist_id" WHERE (a1."id" = $1) [2]

list_albums_by_artist: [
  %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[2018-11-06 13:40:37.109416],
    title: "Portrait In Jazz",
    tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
    updated_at: ~N[2018-11-06 13:40:37.109422]
  },
  %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: 3,
    inserted_at: ~N[2018-11-06 13:40:37.100409],
    title: "You Must Believe In Spring",
    tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
    updated_at: ~N[2018-11-06 13:40:37.100415]
  }
]

10:02:27.411 [debug] QUERY OK source="albums" db=1.6ms

SELECT a0."id", a0."title", a0."inserted_at", a0."updated_at", a0."artist_id" FROM "albums" AS a0 WHERE (a0."artist_id" = $1) [2]

list_albums_by_artist_no_join: [
  %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[2018-11-06 13:40:37.109416],
    title: "Portrait In Jazz",
    tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
    updated_at: ~N[2018-11-06 13:40:37.109422]
  },
  %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: 3,
    inserted_at: ~N[2018-11-06 13:40:37.100409],
    title: "You Must Believe In Spring",
    tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
    updated_at: ~N[2018-11-06 13:40:37.100415]
  }
]
$

The other issue that comes up is whether using an association within a query between entities like Author and Comment even makes sense.

So far in my judgement using Ecto associations within a query makes sense if you want to populate the complete set of associations when you preload.

Given the nature of the relationship between Author and Comment, you’ll often find yourself only wanting a subset like:

  • The most recent Comments from the Author.
  • The most popular Comments from the Author.
  • The Comments from the Author pertaining to a particular Topic.

If the query is only interested in a subset of the existing associations then it likely makes more sense to just forget that the association even exists and to rely purely on Ecto.Query functions that mirror the standard query functions and operators.

PS: Not using preload/3?

|> join(:left, [c], a in assoc(c, :author))

then consider (Expression examples)

|> join(:left, [c], a in Author, a.id == c.author_id)

or (see end of Keywords examples)

|> join(:left, [c], a in Author, [id: c.author_id])

which would have made it clear that the join is extraneous.

6 Likes