Composing Ecto Query By ID

Hello All, I have two schemas, authors and comments with ecto.association to each other. Now, I want to create a query to get all comments by a particular author.

First, I can add comments by different author id, so I have a lot of comments already associated with several authors (1,2,3,4,5,6 and so on). Now I want to get comments by author 1 if the authorId is 1, comments by author 2 if authorId is 2 etc

This is the query I composed already
def list_comments do
Comment
|> join(:left, [c], a in assoc(c, :author))
|> where([c, a], a.id == 1)
|> select([c, _], c)
|> Repo.all()
end

This query only fetches comments from the author with author Id ==1

I want to be able to query comments from other authors too not just from author 1.

Not sure if the query is optimal but something like

def list_comments_by_author(author_id) do
  Comment
  |> join(:left, [c], a in assoc(c, :author))
  |> where([c, a], a.id == ^author_id)
  |> select([c, _], c)
  |> Repo.all()
end
2 Likes

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