Search code examples
postgresqlphoenix-frameworkecto

Will it be possible to add additional fields in many_to_many association in ecto?

I'm a newbie to Ecto. I have three tables defined in Ecto schema called User, Role and UserRole. In UserRole i need to update additional field (such as "status") on associating both the User and Role table, Which will make an entry in UserRole table.

// User Schema
schema "users" do
    field :first_name, :string
    field :last_name, :string
    field :email, :string

    many_to_many :roles, Role, join_through: "userroles"
end

// Role Schema
schema "roles" do
    field :code, :string
    field :description, :string

    many_to_many :users, User, join_through: "userroles"
end

// UserRole Schema
schema "userroles" do
    field :is_default, :boolean, default: false
    field :status, :string
    field :user_id, :id
    field :role_id, :id

    belongs_to :users, User, define_field: false
    belongs_to :roles, Role, define_field: false
end

// The below are the steps i have done

  1. Opened iex using iex -S mix

  2. Inserted a record in User table.

    a. userChangeset = User.changeset(%User{}, %{email: "xyz@gmail.com", first_name: "xyz", last_name: "z"}) b. user1 = Repo.insert!(userChangeset)

  3. Inserted a record in Role table.

    a. roleChangeset = Role.changeset(%Role{}, %{code: "CON", description: "Consumer"}) b. role1 = Repo.insert!(roleChangeset)

  4. Now all fine, I have user record at variable user1 and role record at variable role1 respectively.
  5. Now i need to associate both the records to insert a record at UserRole Table. Which will be created automatically on associating the user1 and role1 record

  6. Associating both the user1 and role1 record by using the below command at iex

    a. userRoleAssoc = user1 |> Repo.preload(:roles) |> Ecto.Changeset.change() |> Ecto.Changeset.put_assoc(:roles, [role1]) |> Repo.update!

  7. Yes it inserts a record at UserRole like as follows enter image description here

  8. But the problem here is, I need to insert the status field to while associating. How do I do that.

  9. I tried updating the UserRole record

    a. fetchUserRole = Repo.get_by(UserRole, id: 1)

    b. fetchUserRole = %{ fetchUserRole | status: "Active"}

    c. fetchUserRole |> Ecto.Changeset.change() |> Repo.update

  10. It had given the following result. In the result it got updated but not reflected in my DB. The result stays as like the above image.

    {:ok, %UserRole{ meta: #Ecto.Schema.Metadata<:loaded, "userroles">, companies: #Ecto.Association.NotLoaded, id: 1, is_default: false, role_id: 1, roles: #Ecto.Association.NotLoaded, status: "Active", user_id: 1, users: #Ecto.Association.NotLoaded }}

  11. My question here is, Is there any way to insert the field value while associating if it is a many_to_many association. If yes means, How to do that.

Solution

  • To actually answer #11, there's a question about your business logic that needs answering:

    When your users are assigning a user to a role, are they creating new roles? Or just selecting from pre-defined roles?

    I would imagine it's the latter. If so, I would imagine you could do the following...

    defmodule YourApp.User do
  use Ecto.Schema
  import Ecto.Changeset

  schema "users" do
    ...
    has_many :user_roles, YourApp.UserRole
  end

  def changeset(user, params) do
    user
    |> cast_things...
    |> cast_assoc(:user_roles)
  end
end

    ...because your Users are never changing the Roles available. Just the UserRoles. Which would let you do something like...

    user = YourApp.Repo.get(YourApp.User, 1)

    user
|> YourApp.User.changeset(%{user_roles: [%{role_id: 1, status: "Active"}]})
|> YourApp.Repo.update

    That's a possibility. However, I personally find working with cast_assoc to be sometimes hard to wrap my mind around, especially given the preloading required and nuanced rules to process, so I tend to work on the join tables directly. That's why I mentioned in my comment above that I'm confused why #9 and #10 aren't working above.