Ecto.Schema associaton through shared foreign key

Questions / Help
1

I’m working on a scenario where I have the following schemas — Assessment, Entry, Feedback, and Student.

the relationship between them is basically:

  1. Assessment can have many entries and feedback
  2. Student can have many entries and feedback
  3. each Student can have only one Entry and one Feedback per Assessment
  4. entries and feedback are individual (each entry and feedback reference only one student)

I already have all of the above rules enforced by constraints in the database, and I have all the schemas in place — except for one specific desired relationship: Entry — Feedback.

the way it’s currently implemented is the following (showing only relevant code):

defmodule App.Assessments.Entry do
  schema "entries" do
    # ... other fields
    field :feedback, :map, virtual: true
    belongs_to :assessment, App.Assessments.Assessment
    belongs_to :student, App.Schools.Student
  end
end

defmodule App.Assessments.Feedback do
  schema "feedback" do
    # ... other fields
    field :entry, :map, virtual: true
    belongs_to :assessment, App.Assessments.Assessment
    belongs_to :student, App.Schools.Student
  end
end

then, to query the virtual field in the schema I have something like

from(
  e in Entries,
  left_join: f in Feedback,
  on: e.assessment_id == f.assessment_id and e.student_id == f.student_id,
  select: %{e | feedback: f}
)

it does work, but I wonder if there’s some configuration combination that I’m missing which would allow me to use has_one (with :through and/or :where maybe?). the reason I want to do that is to leverage the preload/3 functions (in Ecto.Repo and Ecto.Query), and also because I feel that having the “rule” of linking entries with feedback through student + assessment in the schema would be great for documentation and clarity.

thanks!

2

You could add foreign keys to both Entry and Feedback in place of the virtual fields. But it might be better to introduce a new StudentAssessment schema/table and join that to an entry and a feedback.

3

is this a good idea, thinking about DB architecture? (note this is a sincere question. I’m not a DBA, nor do I consider myself a good architect.)

I can see that doing this would allow me to achieve the result I wanted in the schema (the possibility of using has_one :through), but it would come with a cost of a potential DB inconsistency between the already existing student_id and assessment_id and the entry and feedback ids in the proposed join schema/table.

(I think I’ll propose the addition of options to has_one/3 / has_many/3 that would allow this, and see what is the community response)

4

Yes, I think the fk option would open up the possibility of an inconsistency, but I do not think the same is true of the new table, which is why I suggested it would be better. Its student/assessment fks would replace all of the fks and virtual fields on the feedback and entry tables, which would access all of their associated data through the new table.

defmodule App.Assessments.StudentAssessment do
  schema "student_assessments" do
    belongs_to :assessment, App.Assessments.Assessment
    belongs_to :student, App.Schools.Student
    has_one :feedback, App.Assessments.Feedback
    has_one :entry, App.Assessments.Entry
  end
end

defmodule App.Assessments.Entry do
  schema "entries" do
    # ... other fields
    belongs_to :student_assessment, App.Assessments.StudentAssessment

    has_one :feedback, through: [:student_assessment, :feedback]
  end
end

defmodule App.Assessments.Feedback do
  schema "feedback" do
    # ... other fields
    # ... other fields
    belongs_to :student_assessment, App.Assessments.StudentAssessment

    has_one :entry, through: [:student_assessment, :entry]
  end
end

In general, I would recommend avoiding the use of the :where option on associations as I believe it makes things less explicit and therefore more difficult to reason about. From the docs:

Important! Please use this feature only when strictly necessary, otherwise it is very easy to end-up with large schemas with dozens of different associations polluting your schema and affecting your application performance.

1 Like
5

Considering further: the fk for the entry and feedback might also be better on the student assessment.

defmodule App.Assessments.StudentAssessment do
  schema "student_assessments" do
    belongs_to :assessment, App.Assessments.Assessment
    belongs_to :student, App.Schools.Student
    belongs_to :feedback, App.Assessments.Feedback
    belongs_to :entry, App.Assessments.Entry
  end
end

defmodule App.Assessments.Entry do
  schema "entries" do
    # ... other fields
    has_one :student_assessment, App.Assessments.StudentAssessment
    has_one :feedback, through: [:student_assessment, :feedback]
  end
end

defmodule App.Assessments.Feedback do
  schema "feedback" do
    # ... other fields
    # ... other fields
    has_one :student_assessment, App.Assessments.StudentAssessment
    has_one :entry, through: [:student_assessment, :entry]
  end
end

But I think which is better would depend on more details about the use cases.

1 Like
6

ohhhh now I get it. I didn’t understand your proposal in the first reply, but it’s clearer now. thanks for the code snippets.

yes! although I really like what you’re proposing, I need to think more about my specific use case — for example: if I want to keep student and assessment fields in Entry (or Feedback) schema, I would need to add back the associations (but now using a has_one :through instead of a belongs_to).

even if I don’t implement exactly what you’re proposing, it already opened my mind to different approaches. thank you!