Ecto Many-To-Many insertion with additional attributes in association table, need help with approach

[akshayrathod]

I have two tables Student and Teacher let’s suppose for the sake of example. There is a third table, the joining table which has student_id and teacher_id with additional attribute say subject.

I now want to attribute many Students to one Teacher or one Teacher to many Students, where Students records already exist and I want one or more students to be attributed to one Teacher with the subject value for each of them. What would be the best way to do this ? I’m not 100% confident if the schema design is also correct, but the use case is absolute reflection of what I’m trying to do.

I tried using put_assoc for the building association against student, and cast_assoc for the teacher value, and my changeset definition for the joining table looks like this -

def changeset(model, params) do
    model
    |> cast(params, @required)
    |> validate_required(@required)
    |> cast_assoc(:teacher)
    |> put_assoc(:student, params.student)
end

This works for while inserting one student, but when inserting next record the Teacher would exist already and there is a chance it will insert multiple records for the same Teacher.

Also is it possible to get the below two responses for these both models ?

// for Student
    {
        "student": {
            "name": "Mojo Jojo",

            "teachers": [{
                "name": "Prof. Utonium",

                "subject": "Chemistry"
            }]
        }
    }

// for Teacher
    {
        "teacher": {
            "name": "Prof. Utonium",

            "students": [{
                "name": "Mojo Jojo",

                "subject": "Chemistry"
            }]
        }
    }

[mythicalprogrammer]

Welcome to Elixir Forum.

This works for while inserting one student, but when inserting next record the Teacher would exist already and there is a chance it will insert multiple records for the same Teacher.

You just need to check for this. Either a database constraint for uniqueness of combination or in your elixir code.

For many to many with existing record I believe you need to explicitly use join table and use cast_assoc put_assoc. This imply that you need to code out explicitly the join table as a schema. I know you don’t have to in other cases but so far in my experience I’ve made schema for all my join tables since I often use one of the two tables as lookup table.

You can see my thread I made here:

Insert many to many ecto was harder because lack of info

It’s a bit of ranting. I just hope this is constructive. Anyway the problem: I have a perfumes table which have a many to many relationship with companies table. So there is a join table perfume_company_joins (I know I’m very good at naming). Anyway, the companies table is mostly static. It’s like a look up table. Users are able to submit a request to add a new perfume, sign this form html template and add how many company that was involve in building the perfume. The problem is I couldn’t …

[AlchemistCamp]

Hello and welcome! Another option for this kind of schema is to simply cast the IDs like this:

defmodule MyApp.Lessons.TeacherStudent do
  use Ecto.Schema
  import Ecto.Changeset
  alias MyApp.Lessons.{Teacher, TeacherStudent, Student}

  schema "teachers_students" do
    field(:subject, :string)
    belongs_to(:teacher, Teacher)
    belongs_to(:student, Student)

    timestamps()
  end

  @doc false
  def changeset(%TeacherStudent{} = teacher_student, attrs) do
    teacher_student
    |> cast(attrs, [:teacher_id, :student_id, :subject])
    |> validate_required([:teacher_id, :student_id])
  end
end

[akshayrathod]

[Album] Many-To-Many Schema

This is what my schema looks like currently. I do have the third table. If there is a way to tell cast_assoc to not create a new entry based on custom field other than id that would solve the problem of inserting.

Next thing I’m not clear, if I query the Teachers table and preload the Students, I will not be able to see the subject field. Is there a way I can get around this ?

[hlx]

In your case I would just work directly with the TeacherStudent schema. This way you can skip the *_assoc/* functions and use insert_or_update/2 on your TeacherStudent schema.

Renaming TeacherStudent to something more meaningful like Curriculum (in this example) would make matters even more explicit.

[mythicalprogrammer]

This is what my schema looks like currently. I do have the third table. If there is a way to tell cast_assoc to not create a new entry based on custom field other than id that would solve the problem of inserting.

I use put_assoc on the join table (previous post I stated cast_assoc and I was wrong and misspoke).

Next thing I’m not clear, if I query the Teachers table and preload the Students , I will not be able to see the subject field. Is there a way I can get around this ?

Can’t you just preload the join table too? If not a worst case scenario is just create another query.

Here’s my changeset code:

   7   schema "perfumes" do
   8     field :concentration, :string
   9     field :day_released, :integer
  10     field :gender, GenderEnum
  11     field :month_released, MonthEnum
  12     field :perfume_description, :string
  13     field :perfume_name, :string
  14     field :picture_url, :string
  15     field :year_released, :integer
  16
  17     many_to_many :companies, Fumigate.Fragrance.Company,
  18       join_through: Fumigate.Fragrance.PerfumeCompanyJoin,
  19       on_replace: :delete
  20
  21     many_to_many :notes, Fumigate.Fragrance.Note,
  22       join_through: Fumigate.Fragrance.PerfumeNoteJoin,
  23       on_replace: :delete
  24
  25     many_to_many :accords, Fumigate.Fragrance.Accord,
  26       join_through: Fumigate.Fragrance.PerfumeAccordJoin,
  27       on_replace: :delete
  28
  29     has_many :perfume_company_joins,
  30       Fumigate.Fragrance.PerfumeCompanyJoin,
  31       on_replace: :delete
  32     has_many :perfume_accord_joins,
  33       Fumigate.Fragrance.PerfumeAccordJoin,
  34       on_replace: :delete
  35     has_many :perfume_note_joins,
  36       Fumigate.Fragrance.PerfumeNoteJoin,
  37       on_replace: :delete
  38
  39     timestamps()
  40   end
  42   @doc false
  43   def changeset(perfume, attrs) do
  44     company_records = id_records(:company_id, attrs["company"])
  45     accord_records = id_records(:accord_id, attrs["accord_id"])
  46     note_records = get_all_note_records(attrs)
  47
  48     perfume
  49     |> cast(attrs, [:perfume_name, :concentration, :gender, :perfume_description, :picture_url, :year_released, :month_released, :day_released])
  50     |> validate_required([:perfume_name, :gender, :perfume_description])
  51     |> put_assoc(:perfume_company_joins, company_records)
  52     |> put_assoc?(:perfume_accord_joins, accord_records)
  53     |> put_assoc?(:perfume_note_joins, note_records)
  54   end
  55
  56   defp put_assoc?(changeset, _atom, nil), do: changeset
  57   defp put_assoc?(changeset, atom, records) do
  58     changeset
  59     |> put_assoc(atom, records)
  60   end

Here’s a note schema which is simpler than company:

   1 defmodule Fumigate.Fragrance.Note do
   2   use Ecto.Schema
   3   import Ecto.Changeset
   4   import Ecto.Query
   5
   6
   7   schema "notes" do
   8     field :note_name, :string
   9
  10     timestamps()
  11     many_to_many :perfumes, Fumigate.Fragrance.Perfume, join_through: Fumigate.Fragrance.PerfumeNoteJoin
  12   end

And the join table between note and perfume:

   1 defmodule Fumigate.Fragrance.PerfumeNoteJoin do
   2   use Ecto.Schema
   3   import Ecto.Changeset
   4   import Ecto.Query
   5
   6
   7   schema "perfume_note_joins" do
   8     field :pyramid_note, PyramidNoteEnum
   9     belongs_to :note, Fumigate.Fragrance.Note
  10     belongs_to :perfume, Fumigate.Fragrance.Perfume
  11
  12     timestamps()
  13   end

[AlchemistCamp]

You could give your students a has_many relationship with TeacherStudent. In that relation, you can give it a meaningful name (e.g. “classes”). To go that route, you can do something like this:

defmodule MyApp.Lessons.Student do
  use Ecto.Schema
  import Ecto.Changeset
  alias MyApp.Lessons.{Student, TeacherStudent}

  schema "students" do
    field(:name, :string)
    #...
    has_many :classes, TeacherStudent

    timestamps()
  end

  @doc false
  def changeset(student, attrs) do
    student
    |> cast(attrs, [:name]) # and whatever else
    |> validate_required([:name])
  end
end

Then you can add this preload when getting students: Repo.preload(classes: [:teacher]). You’ll have a structure like this (join table IDs left out for simplicity):

{
  student: %{
    id: 1,
    name: "Mojo Jojo",
    classes: [
      %{
        student_id: 1,
        teacher_id: 1
        subject: "Chemistry",
        teacher: %{
          id: 1
          name: "Prof. Utonium",
        }
      },
      %{
        student_id: 1,
        teacher_id: 4
        subject: "Music",
        teacher: %{
          id: 4
          name: "Melody Smith"
        }
      }
    ]
  }
}

If you want to flatten it out and have teachers names together with the subjects in the join table, a helper function in the context might be a reasonable place to do it. Or, you could define a more complex SQL query via Ecto. Defining a JSON encoding for Students would be another way. There are a lot of options. I usually stick with the simple approach above. Mythical programmer’s way of loading the join table separately is something I’ve done, too.

[akshayrathod]

Agreed. This is a much cleaner way to do this.