Insert many to many ecto was harder because lack of info

It’s a bit of ranting.

I just hope this is constructive.

Anyway the problem:

I have a perfumes table which have a many to many relationship with companies table. So there is a join table perfume_company_joins (I know I’m very good at naming).

Anyway, the companies table is mostly static. It’s like a look up table. Users are able to submit a request to add a new perfume, sign this form html template and add how many company that was involve in building the perfume.

The problem is I couldn’t for the life of me find the solution online. All of the solutions out there assume that both companies and perfumes record I’m inserting are new. I’m trying to just say, “Hey man use this existing company record here and here’s the existing company id.”

I kept on bouncing between cast_assoc and put_assoc for my change set (Line 38).

  36     perfume                                                                                               
  37     |> cast(attrs, [:perfume_name, :concentration, :gender, :perfume_description, :picture_url, :            year_released, :month_released, :day_released])                                                           
  38     |> put_assoc(:companies)

After two days of reading and trying different things.

I re-reading the doc and blog post out there for cast_assoc and put_assoc. I thought why not try it on the join table.

IT FREAKING WORKS! Well after dicking around cast_assoc and it didn’t work I tried put_assoc and it worked (Line 38).

  29   def changeset(perfume, attrs) do
  30     company_ids = attrs["company"]
  31     company_id_records = company_ids
  32                          |> Enum.map(
  33                            fn(company_id) -> %{
  34                              company_id: String.to_integer(company_id)                                   
  35                            } end)
  36     perfume                                                                                               
  37     |> cast(attrs, [:perfume_name, :concentration, :gender, :perfume_description, :picture_url, :            year_released, :month_released, :day_released])                                                           
  38     |> put_assoc(:perfume_company_joins, company_id_records)
  41     |> validate_required([:perfume_name, :gender, :perfume_description])
  42   end

Anyway, yeah my bad for the rant. I hope this help someone out there. Yall have an awesome weekend.

2 Likes

Do you even need to convert it to an atom map? wouldn’t something like put_assoc(:perfume_company_joins, attrs["company"]) be nicer?

multiple_select returns a list of ids. put_assoc requires a map or a keyword list.

Tried your idea it doesn’t work.

1 Like

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]
 }}
$
5 Likes