When adding two foreign keys using has_many only one is getting added

What do you mean by “role_loaded is not holding” here? There’s no connection between emp_preload and role_loaded.

It’s hard to say with certainty without the code for these, but neither one takes role_loaded as an argument so it’s not going to have an effect on them.

What happens if you do Repo.insert(role_loaded)?

It all comes down to this:

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.

I don’t really understand how the associated object gets inserted. I don’t have a Repo.insert(role) manually anywhere (other than than the one U just suggested).

Repo.insert(role_loaded) does fix the issue sort of. But if I leave everything else as it I end up w this after: 2 instances of the same role.

 id | name  | value | organization_id | employee_id |     
----+-------+-------+-----------------+-------------+--
 30 | owner | 1     |                 |          29 |
 31 | owner | 1     |               1 |          29 | 

It’s hard to say with certainty without the code for these, but neither one takes role_loaded as an argument so it’s not going to have an effect on them.

def update_employee(employee) do
    u_employee = Ecto.Changeset.change(employee)
    Repo.update(u_employee)
    
  end

def update_organization(organization) do
    u_organization = Ecto.Changeset.change(organization)
    Repo.update(u_organization)
  end

Basically the organization is already inserted and I need to fetch it, and add the role. While the employee is being created here along with the role.

Since this is so hard to do I’ll prob remove the 2nd foreign key constraint organization_id and just add this manually to Role and have no connection to Organization.

One thing I don’t have a good sense of in your situation is what data is user provided (and therefore untrusted) or application provided, especially when it comes to roles (name and value fields). If the data is user provided and you’ll want to repopulate a form if a changeset is invalid, then you’ll most likely want to use cast_assoc/2 to set the roles on the employee, otherwise put_assoc/3 can be used.

def register_owner(attrs, organization) do
  role = Ecto.build_assoc(organization, :roles, name: "owner", value: "1")

  %Employee{}
  |> Employee.registration_changeset(attrs)
  |> Ecto.Changeset.put_assoc(:roles, [role])
  |> Repo.insert()
  |> case do
    {:ok, employee} -> Repo.preload(employee, roles: :organizations) # I'm not sure if this is necessary
    other -> other
  end
end

There’s no need to give up, things aren’t THAT bad!

You will always get better results with questions about Ecto if you are specific about what you are trying to do. In particular, this is important:

This specific task is alternatively phrased as “I want to insert an employee and also create a role associated to an existing organization”, which then translates to Ecto operations:

organization = get_the_org_somehow()

role = Ecto.build_assoc(organization, :roles, %{"name" => "owner", etc})
# role is unsaved and has an organization_id but no employee_id

employee_changeset =
  %Employee{}
  |> Employee.registration_changeset(attrs)
  |> Ecto.Changeset.put_assoc(:roles, [role])
# changeset contains an unsaved Employee with one unsaved Role

result = Repo.insert(employee_changeset)

You might also refactor this to make Employee.registration_changeset take an Organization and do the put_assoc there, to keep things tidy.

How are you actually trying to insert the data?

1. Do you already have the tables of IDs and want to assign them manually?
2. Is an Admin creating an Employee and assigning/creating an organisation?
3. Is an employee creating themself and selecting/creating an organisation?

If the process involves the creation of the employee then its pretty easy as you already have acess to their id in the process. Create a form with a dropdown/text input for the organisation_id in the employee creation form. Obviously display the name if its a dropdown rather than an ID.

For the organisation_ids, if they already exist and you want a selector for them you need to figure out the new way of doing something like this:

<%= select f, :organisation_id, Enum.map(@organisations, fn organisation -> {organisation.name, organisation.id} end) %>

When you create the employee, do so in a case statement like this. You can take the organisation_id from the params, then after the employeee is created you can get the employee’s ID alongside the the organisation_id and just create a new role

organisation = employee_params["organisation_id"] 

case Employees.create_employee(employee_params, etc) do
   {:ok, employee} ->
        Roles.create_role(employee, organisation)

Its just a basic example as I don’t know what kind of params you want the employee to have, but if you do this you can access the organisation_id from the form and you can access the newly created employees ID from the {:ok, employee}

The only other step is to add them as cast values in your changeset

After running this I’m still missing the organization_id like the table in the original post shows. Maybe I have it wrong - when I create Organization before this (getting passed in as organization here) there is no Role associated with it. I guess that is wrong.

Or is the line role = Ecto.build_assoc(organization, :roles, name: "owner", value: "1") supposed to handle this?

Updated: The issue was that I was using using build_assoc on an Organization struct from before it was inserted i.e not using Repo.get() , so it was not associated with an existing record. Seems to be working now!

Yes! Using @jswanner solution which is the same as yours I got it to work.
I was not associating it properly with the organization. I have an idea how this associated insert works now.

A brief outline of how it works for my own future reference:

• create organization (parent 1) and insert into DB.
• pass inserted instance of organization (using Repo.get()) to a create employee function
• use build_assoc to link a role (child) with the organization
• create an employee (parent 2) with a changeset
• using put_attr to link the role with the employee
• since the role was linked to the organization before, now all 3 are linked
• insert employee and role will automatically be inserted, and have both FKs.

Thanks @al2o3cr !!