Ecto INSERT many-to-many with extra foreign key

Phoenix Forum Questions / Help
,
1

Morning/Evening elixir phoenix folks

I am playing with a many to many relationship in ecto. and it started to get interesting for me as a newcomer.

I have 4 tables

  1. Business
  2. User
  3. BusinessUser (the joint through table between business and user, just like a membership)
  4. Role (each user belongs to a role)

Business schema

  schema "businesses" do
    field :name, :string
    ......
    many_to_many :users, TestApp.Accounts.User, join_through: TestApp.Businesses.BusinessUser
    has_many :roles, TestApp.Roles.Role
    ......
  end

BusinessUser schema

The :role must be created along with BusinessUser.

  @primary_key false
  schema "business_user" do
    belongs_to :business, TestApp.Businesses.Business
    belongs_to :user, TestApp.Accounts.User
    belongs_to :role, TestApp.Roles.Role

    timestamps()
  end

The problem

I am trying to create a Business. A current user must be associated and a new role must be created for the BusinessUser role. I couldn’t figure out the way to associate the new :role

Currently

Check the comment for the :thinking: :thinking: :smirk: . My brain is a lil bit stucked, maybe there are other ways of achieving this.

  def create_business(user, attrs \\ %{}) do
    %Business{}
    |> Business.changeset(attrs)
    # Current user is associated fine.
    |> Ecto.Changeset.put_assoc(:users, [user])
    # TODO: How do we create and associate the new role?
    # create everything in one go or create role first then put_assoc? but how?
    |> Repo.insert()
    # since this involve creating new role, do we need TRANSACTION? 
  end

Extra

I am new to Elixir Phoenix Ecto, trying to grasp the ecto way of doing things. :smiling_face_with_three_hearts:

2

First up, many_to_many may not be the right kind of association to use here - one major feature it adds compared to has_many + through: is being writable via put_assoc, but as you’ve noticed there’s no good way to get role_id into the mix.

The tricky part about this operation, to me, is that the Role that’s created is reachable by two paths:

  • the roles association on Business
  • the role association on BusinessUser

This makes a single-changeset approach complicated: you could cast_assoc to roles and get the role correctly associated with the newly-created business, but then you’re back in “how do I get role_id to the right place?” territory.

This seems like a good approach for using Ecto.Multi.insert/4:

Ecto.Multi.new()
|> Ecto.Multi.insert(:business, business_changeset)
|> Ecto.Multi.insert(:role, fn %{business: business} ->
  Ecto.build_assoc(business, :role, some_attributes_for_role)
end)
|> Ecto.Multi.insert(:business_user, fn results ->
  %BusinessUser{
    business: results.business,
    user: existing_user,
    role: results.role
  }
end)
|> Repo.transaction()
|> case do
  {:ok, results} ->
      # each of the inserted structs is available by the operation name above:
      #   * result.business
      #   * result.role
      #   * result.business_user
      #
      # BE AWARE: result.business.roles and result.business.users will be empty because those
      #           are the values after inserting the business. Consider reloading to see all the changes together.

    {:error, :business, error_changeset, _results_so_far} ->
      # This is the format that Ecto.Multi produces errors in.
      #
      # You may want to handle :business separately from :role and :business_user,
      #   since the latter two "shouldn't fail"
end
3 Likes