Belongs_to, multiple foreign keys?

Questions / Help
1

I have students schema, each student has a father and mother, thus, I need to have:

belongs_to :parent, Schools.Parent, [foreign_key: :father_id]

but, then, I can’t add another mother_id foreign key, ex:

belongs_to :parent, Schools.Parent, [foreign_key: :mother_id]

how would I handle this case? as I have mother_id, father_id foreign keys at students table that points to the same table parents

Any idea? aas I need to query all parents of specific students using join.

1 Like
2 
belongs_to :father, Schools.Parent, [foreign_key: :father_id]
belongs_to :mother, Schools.Parent, [foreign_key: :mother_id]

This should work.

4 Likes
3

Thanks, it has worked, however, I am trying to select only specific columns from father and mother, like:

query= from s in Schools.Student, where: s.school_id == ^school_id, join: f in assoc(s, :father), join: m in assoc(s, :mother), select: %{father_mobile: f.mobile_number, mother_mobile: m.mobile_number}

this would result into empty array, however, if I used preload as:

query= from s in Schools.Student, where: s.school_id == ^entity_id, preload: :father, preload: :mother

it will give all the students and their father, mother as expected.

so, why the first query resulted into empty array?

1 Like
4

In your solution, You can call for father or mother of a child, but how do You query the parents?

In reverse, how do You call the children of a parent? They can be linked with mother_id, or father_id.

Maybe You don’t need this information. It’s easy to make a query, but not to think of it as an association

1 Like
5

Yeah I’ll echo what @kokolegorille said. It seems like that approach to modelling will make some queries unnecessarily difficult.

I think what would be best is having a relationships table as a join table between students and parents. That way it is easy to query to see what students a parent is responsible and you could even set the relationship type to guardian, grandmother, or other.

But with that said I am not sure why you’d end up with an empty array from that query. Are you sure that there are some students in the school_id that you are querying?

6

Yes, I am sure, there are some students in the school_id that I am querying…
I will consider a pivot table that joins parents and students, currently if I needed to get a parent’s students, I do a query for parent_id in [ father_id, mother_id ] of students table