I have a Role like this in DB. It is attached to both a organization and a employee. But I cannot get both fields organization_id && employee_id to populate. This what it looks like after inserting an employee and an organization.

 id | name  | value | organization_id | employee_id |    
----+-------+-------+-----------------+-------------+-
 21 | owner | 1     |              |          24     |

Modeling

  schema "employees" do
    has_many :roles, Role

  schema "organizations" do
    has_many :roles, Role

  schema "roles" do
    belongs_to :employee, Employee, foreign_key: :employee_id
    belongs_to :organization, Organization, foreign_key: :organization_id

 create table(:roles) do
      add :organization_id, references(:organizations)
      add :employee_id, references(:employees)

The problem is not with the modelling but with the actual insertion.
Here what I’ve tried but it’s not working. I always get the table at the top w/ a missing FK.

I try to

• associate the employee with the role then update the employee,
• associate the role itself with an organization and then update the org.
• Missing: associating the org with the role and upate the org.

Note: this is a bunch of random stuff trying to make the associations take. I know it’s not pretty.

def register_and_preload_employee(attrs) do
  # build a role instance
    role = Role{
      name: "owner",
      value: "1"
    }
    
    # build employee changeset
    emp_changeset = Employee.registration_changeset(%Employee{}, attrs)
    # assoc role with employee
    emp_changeset = Ecto.Changeset.put_assoc(emp_changeset, :roles, [role])
         #role is inserted
  # Ecto.Changeset<
  #   action: nil,
  #   changes: %{
  #     email: "...
  #   ...
  #     roles: [
  #       #Ecto.Changeset<action: :insert, changes: %{}, errors: [],
  #        data: #Role<>, valid?: true>
  #     ]
  #   },
  #   errors: [],
  #   data: #Employee<>,
  #   valid?: true
  # >
    # get org and build_assoc foreign key
    organization_id = 1
    # get org and preload roles
    organization = Company.get_organization(organization_id) |> Repo.preload(:roles)
    # ATTEMPT to build assoc with org and role
    role_w_org = Ecto.build_assoc(organization, :roles, role)
    #  org is loaded into role
    # %Role{
    #   __meta__: #Ecto.Schema.Metadata<:built, "roles">,
    #   id: nil,
    #   name: "owner",
    #   value: "1",
    #   employee_id: nil,
    #   employee: #Ecto.Association.NotLoaded<association :employee is not loaded>,
    #   organization_id: 1,
    #   organization: #Ecto.Association.NotLoaded<association :organization is not loaded>,
    #   inserted_at: nil,
    #   updated_at: nil
    # }
    
    # insert employee
    case Repo.insert(emp_changeset) do
      {:ok, new_emp} ->
     #preload in case required
        emp_preload =
          Repo.preload(new_emp, :organizations)
          |> Repo.preload(:roles)
        # ATTEMPT  build assoc with org and role
        role_loaded =  Ecto.build_assoc(emp_preload, :roles, role_w_org)
        # both foreign keys here
        # %TurnStile.Role{
        #   __meta__: #Ecto.Schema.Metadata<:built, "roles">,
        #   id: nil,
        #   name: "owner",
        #   value: "1",
        #   employee_id: 24,
        #   employee: #Ecto.Association.NotLoaded<association :employee is not loaded>,
        #   organization_id: 1,
        #   organization: #Ecto.Association.NotLoaded<association :organization is not loaded>,
        #   inserted_at: nil,
        #   updated_at: nil
        # }
        # try to update role via employee update - role_loaded is not holding
        update = update_employee(emp_preload)  #-> Repo.update(...)
        #AND/OR
        # try to update role via organization update since it's preloaded
       update_organization(organization) #-> Repo.update(...)
    
    end
  end

I guess maybe my issue is not understanding where the “child” actually gets inserted (not just in elixir, but in any FK relationship). Since the child Role is not actually inserted by me.