How to seed pivot table relations

Phoenix Forum Questions / Help
1

hello all i want to make a seed to seed a pivot table i have schemas defined as such
–migrations

  defmodule Broker.Repo.Migrations.CreateItem do
  use Ecto.Migration

 def change do
 create table(:items) do
  add :name, :string
  add :weight, :float

  timestamps()
 end

end
end

       defmodule Broker.Repo.Migrations.RoomItems do
       use Ecto.Migration

     def change do
     create table(:default_room_items, primary_key: false) do
     add :room_id, :integer
     add :item_id, :integer
      timestamps()

    end
    end
    end

–models

        defmodule Broker.Item do
        use Broker.Web, :model

    schema "items" do
    field :name, :string
    field :weight, :float

    timestamps()
    end

       @doc """
      Builds a changeset based on the `struct` and `params`.
      """
     def changeset(struct, params \\ %{}) do
     struct
     |> cast(params, [:name, :weight])
     |> validate_required([:name, :weight])
     end
     end

     defmodule Broker.DefaultRoomItem do
     use Broker.Web, :model

      schema "default_room_items" do
      field :room_id, :integer
      field :item_id, :integer

     has_many :rooms, Broker.Room
     has_many :items, Broker.Item

     timestamps()
     end

     @doc """
     Builds a changeset based on the `struct` and `params`.
     """
   def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:room_id, :item_id])
    |> validate_required([:room_id, :item_id])
    end
   end

–seeds

     build_default_items = fn items, roomid  ->
     Enum.each(items, fn x ->
     Repo.insert!(%DefaultRoomItem{ item_id: x, room_id: roomid })
     end)
    IO.puts "Inserted #{roomid} "
    end

   bedroom_items = [1,2,2,3]
   build_default_items.(bedroom_items, 1)

the error i get is

   ERROR (undefined_column): column "id" does not exist
    (ecto) lib/ecto/adapters/sql.ex:463: Ecto.Adapters.SQL.struct/6
    (ecto) lib/ecto/repo/schema.ex:397: Ecto.Repo.Schema.apply/4
    (ecto) lib/ecto/repo/schema.ex:193: anonymous fn/11 in Ecto.Repo.Schema.do_insert/4
    (ecto) lib/ecto/repo/schema.ex:614: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto) lib/ecto/adapters/sql.ex:508: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
    (db_connection) lib/db_connection.ex:1275: DBConnection.transaction_run/4
    (db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
    (db_connection) lib/db_connection.ex:790: DBConnection.transaction/3
    (ecto) lib/ecto/repo/schema.ex:124: Ecto.Repo.Schema.insert!/4
    (elixir) lib/enum.ex:645: Enum."-each/2-lists^foreach/1-0-"/2
    (elixir) lib/enum.ex:645: Enum.each/2
    priv/repo/seeds.exs:118: anonymous fn/2 in :elixir_compiler_1.__FILE__/1
    priv/repo/seeds.exs:139: (file)
    (elixir) lib/code.ex:370: Code.require_file/2
    (mix) lib/mix/tasks/run.ex:82: Mix.Tasks.Run.run/1
    (mix) lib/mix/task.ex:294: Mix.Task.run_task/3
    (mix) lib/mix/cli.ex:58: Mix.CLI.run_task/2

how do i get around this

2

This should not be. Yes I know it ‘can’ take structs straight, but I wish it really did not because it causes issues like this. Use your changeset instead, so something like this to replace that line:

Repo.insert!(DefaultRoomItem.changeset(%DefaultRoomItem{}, %{item_id: x, room_id: roomid})
3

i made the changes but the error is the same
build_default_items = fn items, roomid ->
Enum.each(items, fn x ->
Repo.insert!(Broker.DefaultRoomItem.changeset(%DefaultRoomItem{}, %{ item_id: x, room_id: roomid }))
end)
IO.puts "Inserted #{roomid} "
end

4

Oooh, your default_room_items has no primary key at all because create table(:default_room_items, primary_key: false) do, and yet your schema definition of schema "default_room_items" do is saying that is ‘does’ have a primary key, right above that schema line you need to add I think it was @primary_key false. :slight_smile:

5

making progress the error now is set :references option for association :rooms when schema has no primary key

the fix for this is to set the referenced table fk in the model as such

defmodule Broker.DefaultRoomItem do
  use Broker.Web, :model

  @primary_key false
  schema "default_room_items" do
    field :room_id, :integer
    field :item_id, :integer

    has_many :rooms, Broker.Room, references: :room_id
    has_many :items, Broker.Item,  references: :item_id

    timestamps()
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:room_id, :item_id])
    |> validate_required([:room_id, :item_id])
  end
end
1 Like