Array of foreign keys in Ecto (Phoenix)

Question

I'm a beginner in web dev so excuse my lack of knowledge.

I have a Teacher and a Student Ecto schema. They are supposed to be linked via another schema called Class by following these rules:

Each class has only one teacher, and has an array of students.
Each teacher can be part of many classes
Each student can be part of many classes.

Here's the schema I've built so far:

# Part of student.ex
schema "students" do
    field :active, :boolean, default: false
    field :birthday, :date
    field :email, :string, unique: true
    field :firstname, :string
    field :lastname, :string
    field :phone, :string, unique: true
    belongs_to :classes, Class, foreign_key: :class_id
    many_to_many :teachers, Users.Teacher, join_through: "classes"

    timestamps()
  end

# Part of teacher.ex
schema "teachers" do
    field :active, :boolean, default: false
    field :birthday, :date
    field :email, :string, unique: true
    field :firstname, :string
    field :lastname, :string
    field :phone, :string, unique: true
    belongs_to :classes, Class, foreign_key: :class_id
    many_to_many :students, Users.Student, join_through: "classes"
    timestamps()
  end

# Part of class.ex
schema "classes" do
    field :end_date, :date
    field :time, :time
    field :level, :string
    field :start_date, :date
    field :title, :string
    has_many :students, Users.Student
    has_one :teacher, Users.Teacher
    embeds_many :sessions, Session
    timestamps()
  end

Things look ok here. But the problem is, how do I specify "an array of student ids" in my migration file? Here are the migration functions:

# Part of students migration file. It's the same for teachers.
def change do
    create table(:students) do
      add :firstname, :string, null: false, size: 32
      add :lastname, :string, null: false, size: 32
      add :phone, :string, null: false, size: 16
      add :email, :string, size: 32
      add :birthday, :date
      add :active, :boolean, default: false, null: false

      timestamps()
    end

    create(unique_index(:students, [:phone]))
  end

This is where I'm really stuck at right now:

def change do
    create table(:classes) do
      add :title, :string, null: false
      add :level, :string
      add :hours, :string, null: false
      add :start_date, :date
      add :end_date, :date
      add :teacher_id, references(:teachers), primary_key: true
      # HERE! How do I create a column for an array of student_id foreign keys?
      timestamps()
    end

    create(index(:classes, [:teacher_id]))

  end

Thanks in advance.

Aleksei Matiushkin Aleksei Matiushkin · Accepted Answer · 2020-04-22T06:46:54

As it is stated in docs for Ecto.Schema.belongs_to/3

You should use belongs_to in the table that contains the foreign key. Imagine a company <-> employee relationship. If the employee contains the company_id in the underlying database table, we say the employee belongs to company. In fact, when you invoke this macro, a field with the name of foreign key is automatically defined in the schema for you.

On the contrary, neither Ecto.Schema.has_one/3 nor Ecto.Schema.has_many/3 implies the existence of the respective field. Everything gets done by the other end of this link.

That said,

students schema should have class_id field (already there)
classes probably should have teacher_id (assuming a teacher might have several classes.) To do that, alter teachers to has_many :classes instead of belongs_to :class and alter classes to belongs_to :teacher instead of has_one :teacher.

Whether you want the student to belong to many classes, the schema is not correct. The existence of class_id foreign key in each record of students implies each student has this particular class associated. You should remove

belongs_to :classes, Class, foreign_key: :class_id

from students, because it sticks one student to one class, in the first place. Then you have to introduce classes_to_students join schema, as described here Ecto.Schema.many_to_many/3.

Array of foreign keys in Ecto (Phoenix)

1 Answers