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

chrisdel101 · May 31, 2023, 4:33pm

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.

al2o3cr · May 31, 2023, 4:44pm

chrisdel101:

        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

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

        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(...)

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)?

chrisdel101 · May 31, 2023, 8:11pm

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.

jswanner · May 31, 2023, 9:58pm

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

al2o3cr · May 31, 2023, 10:22pm

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.

GazeIntoTheAbyss · June 1, 2023, 6:42am

How are you actually trying to insert the data?

Do you already have the tables of IDs and want to assign them manually?
Is an Admin creating an Employee and assigning/creating an organisation?
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

chrisdel101 · June 1, 2023, 4:23pm

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!

chrisdel101 · June 1, 2023, 4:45pm

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 !!