many_to_many relation through has_many, with additional field in join table

Phoenix Forum Questions / Help
,
1

Hi all!
I am new in phoenix and elixir. Please, help. I get a problem with getting data from join table.

I have 3 tables: “employees”, “offices” and join table “employees_offices”
Employee has array of roles in office.

Employees:
schema “employees” do
has_many :employees_offices, EmployeesOffices, on_replace: :delete
has_many :offices, through: [:employees_offices, :office]
end

def changeset(%Employee{} = employee, attrs) do
    employee
    |> cast(attrs, @required_fields ++ @optional_fields)
    |> validate_required(@required_fields)
    |> cast_assoc(:employees_offices)
  end

Offices:
schema “offices” do
has_many :employees_offices, EmployeesOffices, on_replace: :delete
has_many :employees, through: [:employees_offices, :employee]

    belongs_to :owner, Employee
 
    field :title, :string
    field :address, :string

    timestamps()
  end

  @required_fields ~w(title address)a
  @optional_fields ~w(owner_id)a

  def changeset(%Office{} = office, attrs) do
    office
    |> cast(attrs, @required_fields ++ @optional_fields)
    |> validate_required(@required_fields)
    |> assoc_constraint(:owner)
  end

Join table:
schema “employees_offices” do
belongs_to :office, Office
belongs_to :employee, Employee

    field :roles, {:array, OfficeRolesEnum}, default: []

    timestamps()
  end

  @required_fields ~w(office_id employee_id roles)a
  @optional_fields ~w()a

  def changeset(%EmployeesOffices{} = employee_office, attrs) do
    employee_office
    |> cast(attrs, @required_fields ++ @optional_fields)
    |> validate_required(@required_fields)
    |> assoc_constraint(:office)
    |> assoc_constraint(:employee)
  end

Now i need to get data in next format:
%{
id: employee.id,
roles: %{
role: role1, offices: [office1, office2],
role: role2, offices: [office2, office3]
}
}

How can i get it? Either through complex query or reorgonize data which i get?

1 Like
2

Without questioning your decisions and changing (much) your schemas:

Add a virtual field :roles to the Employee schema.

def get_employee(employee_id) do
  case Repo.get(Employee, employee_id) do
    employee = %Employee{} ->
      {:ok, %Employee{employee | roles: list_roles_for_employee(employee)}}

    nil ->
      {:error, :not_found}
  end
end

def list_roles_for_employee(%Employee{id: employee_id}) do
  from(eo in EmployeesOffices, where: employee_id == ^employee_id)
  |> Repo.all()
  |> Enum.flat_map(fn eo -> Enum.map(eo.roles, &%{office: eo.office_id, role: &1}) end)
  |> Enum.group_by(& &1.role, & &1.office)
end

Completely untested! But I hope this gives you some thoughts to move forward and maybe consider reshaping your schemas. The output of the code above should look something like

%Employee{
  id: xx,
  roles: %{
    role1: [office1, office2],
    role2: [office2, office3]
  }
}
3

It partly solved my problem. Now I can show the data, but how to create it form such structure?

%Employee{
  id: xx,
  roles: %{
    role1: [office1, office2],
    role2: [office2, office3]
  }
}