Trouble with many to many duplicates

Phoenix Forum Questions / Help
,
1

I have this situation:

I have 5 entities, User, Person, Student, Resource and Borrowing.

Borrowing, Student and Resource are related as “many-to-many” and have an intermediate table called “borrowing_resources”.

I want to save a Borrowing and needs User, Person, Student and Resource, then i make the next process:

iex(1)> alias AudioVisuales.{Repo, User, Person, Student, Resource, Borrowing}
[AudioVisuales.Repo, AudioVisuales.User, AudioVisuales.Person,
 AudioVisuales.Student, AudioVisuales.Resource, AudioVisuales.Borrowing]

iex(2)> user = Repo.get!(User, "1094978564")
[debug] QUERY OK source="user" db=2.1ms decode=3.5ms queue=7.2ms idle=216.5ms
SELECT u0.`id_number`, u0.`name`, u0.`lastname`, u0.`phone_number`, u0.`email`, u0.`password`, u0.`role`, u0.`inserted_at`, u0.`updated_at` FROM `user` AS u0 WHERE (u0.`id_number` = ?) ["1094978564"]
←[90mÔå│ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309←[0m
%AudioVisuales.User{
  __meta__: #Ecto.Schema.Metadata<:loaded, "user">,
  id_number: "1094978564",
  name: "Juan",
  lastname: "Adarve",
  phone_number: "3148298986",
  email: "juanm@uniquindio.edu.co",
  password: "123",
  role: :administrator,
  created_borrowings: #Ecto.Association.NotLoaded<association :created_borrowings is not loaded>,
  closed_borrowings: #Ecto.Association.NotLoaded<association :closed_borrowings is not loaded>,
  inserted_at: ~N[2023-04-30 00:00:00],
  updated_at: ~N[2023-04-30 00:00:00]
}

iex(3)> person = Repo.get!(Person, "1548646545")
[debug] QUERY OK source="person" db=1.9ms queue=2.3ms idle=1844.9ms
SELECT p0.`id_number`, p0.`name`, p0.`lastname`, p0.`phone_number`, p0.`email`, p0.`undergraduate_program_id`, p0.`inserted_at`, p0.`updated_at` FROM `person` AS p0 WHERE (p0.`id_number` = ?) ["1548646545"]
←[90mÔå│ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309←[0m
%AudioVisuales.Person{
  __meta__: #Ecto.Schema.Metadata<:loaded, "person">,
  id_number: "1548646545",
  name: "Julian",
  lastname: "Gutierrez",
  phone_number: "3541334",
  email: "jugutier@uniquindio.edu.co",
  undergraduate_program_id: 1,
  undergraduate_program: #Ecto.Association.NotLoaded<association :undergraduate_program is not loaded>,
  teacher: #Ecto.Association.NotLoaded<association :teacher is not loaded>,
  student: #Ecto.Association.NotLoaded<association :student is not loaded>,
  borrowings: #Ecto.Association.NotLoaded<association :borrowings is not loaded>,
  inserted_at: ~N[2023-04-30 00:00:00],
  updated_at: ~N[2023-04-30 00:00:00]
}

iex(4)> student = Repo.get!(Student, "54645")
[debug] QUERY OK source="student" db=1.6ms queue=2.0ms idle=1589.1ms
SELECT s0.`person_id` FROM `student` AS s0 WHERE (s0.`person_id` = ?) ["54645"]
←[90mÔå│ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309←[0m
%AudioVisuales.Student{
  __meta__: #Ecto.Schema.Metadata<:loaded, "student">,
  person_id: "54645",
  person: #Ecto.Association.NotLoaded<association :person is not loaded>,
  borrowings: #Ecto.Association.NotLoaded<association :borrowings is not loaded>,
  resources: #Ecto.Association.NotLoaded<association :resources is not loaded>
}

iex(5)> resource = Repo.get!(Resource, 1)
[debug] QUERY OK source="resource" db=2.2ms queue=2.5ms idle=24.5ms
SELECT r0.`id`, r0.`name`, r0.`inventory_tag`, r0.`serial_tag`, r0.`state`, r0.`description`, r0.`type_resource_id`, r0.`inserted_at`, r0.`updated_at` FROM `resource` AS r0 WHERE (r0.`id` = ?) [1]
←[90mÔå│ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309←[0m
%AudioVisuales.Resource{
  __meta__: #Ecto.Schema.Metadata<:loaded, "resource">,
  id: 1,
  name: "Computador #1",
  inventory_tag: "46878845",
  serial_tag: "MXL1154SDFS",
  state: "availible",
  description: "sfgdfgdfg",
  type_resource_id: 1,
  type_resource: #Ecto.Association.NotLoaded<association :type_resource is not loaded>,
  borrowings: #Ecto.Association.NotLoaded<association :borrowings is not loaded>,
  students: #Ecto.Association.NotLoaded<association :students is not loaded>,
  inserted_at: ~N[2023-04-30 00:00:00],
  updated_at: ~N[2023-04-30 00:00:00]
}

iex(6)> borrowing = %Borrowing{description: "test"} |> Repo.preload([:borrowing_opened_user, :person, :resources, :students])

iex(7)> borrowing_changeset3 = Ecto.Changeset.change(borrowing_changeset2) |> Ecto.Changeset.put_assoc(:resources, [resource])
#Ecto.Changeset<
  action: nil,
  changes: %{
    borrowing_opened_user: #Ecto.Changeset<action: :update, changes: %{},
     errors: [], data: #AudioVisuales.User<>, valid?: true>,
    person: #Ecto.Changeset<action: :update, changes: %{}, errors: [],
     data: #AudioVisuales.Person<>, valid?: true>,
    resources: [
      #Ecto.Changeset<action: :update, changes: %{}, errors: [],
       data: #AudioVisuales.Resource<>, valid?: true>
    ]
  },
  errors: [],
  data: #AudioVisuales.Borrowing<>,
  valid?: true
>
iex(8)> borrowing_changeset4 = Ecto.Changeset.change(borrowing_changeset3) |> Ecto.Changeset.put_assoc(:students, [student])
#Ecto.Changeset<
  action: nil,
  changes: %{
    borrowing_opened_user: #Ecto.Changeset<action: :update, changes: %{},
     errors: [], data: #AudioVisuales.User<>, valid?: true>,
    person: #Ecto.Changeset<action: :update, changes: %{}, errors: [],
     data: #AudioVisuales.Person<>, valid?: true>,
    resources: [
      #Ecto.Changeset<action: :update, changes: %{}, errors: [],
       data: #AudioVisuales.Resource<>, valid?: true>
    ],
    students: [
      #Ecto.Changeset<action: :update, changes: %{}, errors: [],
       data: #AudioVisuales.Student<>, valid?: true>
    ]
  },
  errors: [],
  data: #AudioVisuales.Borrowing<>,
  valid?: true
>

iex(9)> Repo.insert!(borrowing_changeset4)
[debug] QUERY OK db=2.2ms queue=0.1ms idle=913.1ms
begin []
←[90mÔå│ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309←[0m
[debug] QUERY OK db=2.3ms
INSERT INTO `borrowing` (`borrowing_opening_user_id`,`description`,`person_id`,`inserted_at`,`updated_at`) VALUES (?,?,?,?,?) ["1094978564", "test", "1548646545", ~N[2023-05-02 18:45:32], ~N[2023-05-02 18:45:32]]
←[90mÔå│ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309←[0m
[debug] QUERY OK db=2.4ms
**INSERT INTO `borrowing_resources` (`borrowing_id`,`resource_id`) VALUES (?,?) [1, 1]**
←[90mÔå│ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309←[0m
[debug] QUERY OK db=2.0ms
**INSERT INTO `borrowing_resources` (`borrowing_id`,`student_id`) VALUES (?,?) [1, "54645"]**
←[90mÔå│ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309←[0m
[debug] QUERY OK db=24.5ms
commit []
←[90mÔå│ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309←[0m

to table ''borrowing_resources" is doing 2 ‘insert into’

INSERT INTO borrowing_resources (borrowing_id,resource_id) VALUES (?,?) [1, 1]

and

INSERT INTO borrowing_resources (borrowing_id,student_id) VALUES (?,?) [1, “54645”]

would should do a INSERT INTO thus:

INSERT INTO borrowing_resources (borrowing_id,student_id,resource_id) VALUES (?,?,?) [1, “54645”,1]

but, it no happening. What happend?

2

It’s hard to say with certainty what’s going on without seeing the schemas, but I don’t think many_to_many will do what you want. It describes a relationship between two entities, and what you’ve described is three (borrowing / student / resource).

Even if such a thing were possible, the semantics seem unclear - you have a Borrowing with associations students and resources, but what does that mean if there are more than one of each? Concretely, imagine we have this Borrowing:

  students: [student1, student2, student3],
  resources: [resource1, resource2]

Should this mean there are six rows in borrowing_resources? (one for each pairing of a student with a resource?)

If not, what does it mean? How would it look different if it did mean six rows?

2 Likes