query to search between two foreign keys in a self join table

Question

Example data in User table

Expected result in rails console, father.children and mother.children should return the same group of objects:

father = User.first
father.children
=> #<ActiveRecord::Associations::CollectionProxy [#<User id: 3, name: "son", father_id: 1, mother_id: 2>, #<User id: 4, name: "daughter", father_id: 1, mother_id: 2>]>
mother = User.find(2)
mother.children
=> #<ActiveRecord::Associations::CollectionProxy [#<User id: 3, name: "son", father_id: 1, mother_id: 2>, #<User id: 4, name: "daughter", father_id: 1, mother_id: 2>]>

This is my association now in User.rb model. It returns the expected result if I do father.children since the foreign_key refers to father_id. But it does not work for mother.children as it does not refer to mother_id.

 has_many :children, foreign_key: "father_id", class_name: "User"
 belongs_to :mother, class_name: "User", optional: true
 belongs_to :father, class_name: "User", optional: true

Is there a way for me to do

foreign key: "father_id" OR "mother_id"

I also tried having queries in the association, but couldn't seem to get my head around it.

 has_many :children, -> { where("father_id = id OR mother_id = id") }, class_name: "User"

Answer 1

You can unscope the default where that query creates, and then use the user id to filter the children rows:

has_many :children,
  ->(user) { unscope(:where).where('father_id = :id OR mother_id = :id', id: user.id) },
  class_name: "User"

Why unscope? Without removing it, your code will result in two SQL queries like these:

SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ?  [["LIMIT", 1]]
SELECT "users".* FROM "users" WHERE "users"."user_id" = ? AND "users"."father_id" = ?  [["user_id", 1], ["father_id", "1"]]

When doing User.first.children - at least in Rails 6.1.4.

As you can see, the second query filters all those users with id equals to receiver (1) and at the same time, having the father_id column equals to the receiver (1). Which looking at your code, might never happen.

Notice, if you don't really need all those methods added to your model after declaring a has_many relationship, you might be better using a single instance method, or a scope (in the last case). Because using this approach, you override the default behavior (documented nowhere), that's filtering the rows in the other table by the primary key in this one.