Insert many to many ecto was harder because lack of info

[peerreynders]

@mythicalprogrammer

“Hey man use this existing company record here and here’s the existing company id.”

See: Example: Adding tags to a post

TLDNR: For put_assoc to work as expected it needs to be handed full schema structs, not just IDs (plain maps or keyword lists lead to Ecto trying to insert a new record on the other end of the association).

This works without referring to the association table:

  # Usually in MusicDB.Album
  def changeset(album, params \\ %{}) do
    # obtain genres as full schema structs
    genres = fetch_selected_genres(params["genres"])

    album
    |> EC.cast(params, [:title])
    |> EC.put_assoc(:genres, genres)
    |> EC.validate_required([:title])
  end

(I also find that it is usually necessary to show the schemas involved to make sense of some of the changeset and association code)

# file: lib/music_db/artist.ex

defmodule MusicDB.Artist do
  use Ecto.Schema
  import Ecto.Changeset
  alias MusicDB.{Artist, Album}

  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

  def changeset(artist, params) do
    artist
    |> cast(params, [:name, :birth_date, :death_date])
    |> validate_required([:name])
  end

  def changeset(%MusicDB.Band{} = band) do
    {:ok, birth_date} = Date.new(band.year_started, 1, 1)
    {:ok, death_date} = Date.new(band.year_ended, 12, 31)

    changeset(
      %Artist{
        name: band.name,
        birth_date: birth_date,
        death_date: death_date
      },
      %{}
    )
  end

  def changeset(%MusicDB.SoloArtist{} = solo_artist) do
    name =
      "#{solo_artist.name1} #{solo_artist.name2} #{solo_artist.name3}"
      |> String.trim()

    changeset(
      %Artist{
        name: name,
        birth_date: solo_artist.birth_date,
        death_date: solo_artist.death_date
      },
      %{}
    )
  end
end

# file: lib/music_db/album.ex

defmodule MusicDB.Album do
  use Ecto.Schema
  alias MusicDB.{Artist, Track, Genre}

  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
end

# file: lib/music_db/album_genre.ex

defmodule MusicDB.AlbumGenre do
  use Ecto.Schema
  alias MusicDB.{Album, Genre}

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

# file: lib/music_db/genre.ex

defmodule MusicDB.Genre do
  use Ecto.Schema
  alias MusicDB.Album

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

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

# 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: "#{app}: #{vsn}"

  defp map_app_name({app, _}),
    do: app
end

defmodule Playground do
  import Ecto.Query
  alias Ecto.Changeset, as: EC
  alias Ecto, as: E
  alias MusicDB.Repo
  alias MusicDB.{Artist, Album, Genre}

  def query_bill_evans do
    from(a in Artist, where: a.name == "Bill Evans")
    |> Repo.one()
  end

  def cons_integer(text, values) do
    case Integer.parse(text) do
      {value, _rest} ->
        [value | values]
      _ ->
        values
    end
  end

  def fetch_selected_genres(selected) do
    ids = Enum.reduce(selected, [], &cons_integer/2)

    case ids do
      [] = empty ->
        empty
      _ ->
        from(g in Genre, where: g.id in ^ids)
        |> Repo.all()
    end
  end

  # Usually in MusicDB.Album
  def changeset(album, params \\ %{}) do
    # obtain genres as full schema structs
    genres = fetch_selected_genres(params["genres"])

    album
    |> EC.cast(params, [:title])
    |> EC.put_assoc(:genres, genres)
    |> EC.validate_required([:title])
  end

  def make_params do
    %{
      "title" => "Test Album",
      "genres" => ["1","2"]
    }
  end

  def play do
    # Note: Album needs to be associated with
    # with an Artist so we use build_assoc
    # to create the "shell" schema struct
    # for the Album
    #
    query_bill_evans()
    |> E.build_assoc(:albums)
    |> changeset(make_params())
    |> Repo.insert()
  end
end

IO.puts(AppInfo.string())
IO.inspect(Playground.play())

$ mix run ./priv/repo/playground.exs
asn1: 5.0.9, compiler: 7.4.4, connection: 1.0.4, crypto: 4.5.1, db_connection: 2.0.5, decimal: 1.6.0, ecto: 3.0.6, ecto_sql: 3.0.5, elixir: 1.9.1, hex: 0.20.1, inets: 7.0.9, jason: 1.1.2, kernel: 6.4.1, logger: 1.9.1, mariaex: 0.9.1, mix: 1.9.1, music_db: 0.1.0, postgrex: 0.14.1, public_key: 1.6.7, ssl: 9.3.5, stdlib: 3.9.2, telemetry: 0.3.0

18:46:13.559 [debug] QUERY OK source="artists" db=4.4ms decode=0.6ms queue=1.2ms
SELECT a0."id", a0."name", a0."birth_date", a0."death_date", a0."inserted_at", a0."updated_at"
FROM "artists" AS a0 WHERE (a0."name" = 'Bill Evans') []

18:46:13.566 [debug] QUERY OK source="genres" db=0.8ms queue=0.9ms
SELECT g0."id", g0."name", g0."wiki_tag", g0."inserted_at", g0."updated_at"
FROM "genres" AS g0 WHERE (g0."id" = ANY($1)) [[2, 1]]

18:46:13.569 [debug] QUERY OK db=0.2ms
begin []

18:46:13.574 [debug] QUERY OK db=1.2ms
INSERT INTO "albums" ("artist_id","title","inserted_at","updated_at")
VALUES ($1,$2,$3,$4) RETURNING "id" [2, "Test Album", ~N[2019-08-03 22:46:13], ~N[2019-08-03 22:46:13]]

18:46:13.576 [debug] QUERY OK db=0.7ms
INSERT INTO "albums_genres" ("album_id","genre_id")
VALUES ($1,$2) [16, 1]

18:46:13.577 [debug] QUERY OK db=0.2ms
INSERT INTO "albums_genres" ("album_id","genre_id")
VALUES ($1,$2) [16, 2]

18:46:13.583 [debug] QUERY OK db=6.0ms
commit []
{:ok,
 %MusicDB.Album{
   __meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
   artist: #Ecto.Association.NotLoaded<association :artist is not loaded>,
   artist_id: 2,
   genres: [
     %MusicDB.Genre{
       __meta__: #Ecto.Schema.Metadata<:loaded, "genres">,
       albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
       id: 1,
       inserted_at: ~N[2019-08-03 14:35:27],
       name: "jazz",
       updated_at: ~N[2019-08-03 14:35:27],
       wiki_tag: "Jazz"
     },
     %MusicDB.Genre{
       __meta__: #Ecto.Schema.Metadata<:loaded, "genres">,
       albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
       id: 2,
       inserted_at: ~N[2019-08-03 14:35:27],
       name: "live",
       updated_at: ~N[2019-08-03 14:35:27],
       wiki_tag: "Concert"
     }
   ],
   id: 16,
   inserted_at: ~N[2019-08-03 22:46:13],
   title: "Test Album",
   tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
   updated_at: ~N[2019-08-03 22:46:13]
 }}
$