CODEWITHSUNDEEP
elixirecto
Phu Anh Tuan Nguyen
Phu Anh Tuan Nguyen

Reputation: 63

ecto - set field in intermediate table in many to many relationship

I have two entities Users and Rooms with a many to many relationship.

I've added an additional field permissionto the intermediate table

Migration:

 def change do
    create table(:room_users) do
      add :permission, :string
      add :user_id, references(:users)
      add :room_id, references(:rooms)
    end

    create unique_index(:room_users, [:user_id, :room_id])
  end

Schemas:

 schema "rooms" do
    field :name, :string
    many_to_many :users, User, join_through: "room_users"

    timestamps()
  end

...

  schema "users" do
    field :username, :string
    many_to_many :rooms, Room, join_through: "room_users"
    timestamps()
  end

I've written a changeset that associates a user with a room, which works

  def changeset_update_user(room, user) do
    room
    |> cast(%{}, [:name])
    |> validate_required([:name])
    |> put_assoc(:users, [user])
  end

Now the question: whenever I associate a user to a room I also want to set the permission field. Something like

  def changeset_update_user(room, user) do
    room
    |> cast(%{}, [:name])
    |> validate_required([:name])
    |> put_assoc(:users, [%{user_id: user.id, permission: "HOST"}])
  end

EDIT: function to associate user with room

  def create_Room_with_User(attrs \\ %{}, user) do
    {result, room_dto} = %Room{}
                    |> Room.changeset(attrs)
                    |> Repo.insert()

    Repo.preload(room_dto, :users)
    |> Room.changeset_update_user(user)
    |> Repo.update()

    {result, room_dto}
  end

But then I get an error that user_id and permission are unknown fields

Upvotes: 1

Views: 746

Answers (1)

sabiwara
sabiwara

Reputation: 3204

You are getting an unknown field :permission because this is not a User field, but a RoomUser field.

As explained in the docs, many_to_many/3 is a convenience when you don't need to care about the intermediate table: since you need permission, this is not the case. Quoting the doc:

If you need to access the join table, then you likely want to use has_many/3 with the :through option instead.

The idea is described here.

Translated in your example, this becomes:

defmdoule Room do
  use Ecto.Schema

  schema "rooms" do
    field :name, :string
    has_many :room_users, RoomUser
    has_many :users, through: [:room_users, :user]
  end
end

defmodule User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    has_many :room_users, RoomUser
    has_many :rooms, through: [:room_users, :room]
  end
end

defmodule RoomUser do
  use Ecto.Schema

  schema "room_users" do
    field :permission, :string
    belongs_to :user, User
    belongs_to :room, Room
  end
end

You can then replace your put_assoc with:

|> put_assoc(:room_users, [%{user: user, permission: "HOST"}])

Upvotes: 3

Related Questions