Association for the child of the child in ruby on rails

Example of data in User table

Expected result in rails console, grandfather.grandchildren and grandmother.grandchildren should return the same group of objects:

grandfather = User.first
grandfather.grandchildren
=> #<ActiveRecord::Associations::CollectionProxy [#<User id: 5, name: "father's son", father_id: 3, mother_id: nil>, #<User id: 6, name: "uncle's son", father_id: 4, mother_id: nil>]>
grandmother = User.find(2)
grandmother.grandchildren
=> #<ActiveRecord::Associations::CollectionProxy [#<User id: 5, name: "father's son", father_id: 3, mother_id: nil>, #<User id: 6, name: "uncle's son", father_id: 4, mother_id: nil>]>

This is my association now in User.rb model.

 has_many :children, ->(user) { unscope(:where).where("father_id = :id OR mother_id = :id", id: user.id) }, class_name: "User"
 has_many :grandchildren, through: :children, source: :children
 belongs_to :mother, class_name: "User", optional: true
 belongs_to :father, class_name: "User", optional: true

Output in rails console now:

irb(main):001:0> grandfather = User.first
   (0.3ms)  SELECT sqlite_version(*)
  User Load (0.2ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ?  [["LIMIT", 1]]
=> #<User id: 1, name: "grandfather", mother_id: nil, father_id: nil>

irb(main):002:0> grandfather.grandchildren
  User Load (0.3ms)  SELECT "users".* FROM "users" INNER JOIN "users" "children_grandchildren" ON "users"."user_id" = "children_grandchildren"."id" WHERE (father_id = 1 OR mother_id = 1) /* loading for inspect */ LIMIT ?  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (SQLite3::SQLException: ambiguous column name: father_id)

Answer

You can’t get the grandchildren from a grandparent by going through its children because it implies that their father/mother ids are equal to the grandparent, it doesn’t travel through the grandchildren parents:

SELECT "users".*
FROM "users"
INNER JOIN "users" "children_grandchildren"
ON "users"."user_id" = "children_grandchildren"."id"
WHERE (father_id = 1 OR mother_id = 1) -- this is the grandparent id, when it should be the child parent's id

You can add a callable to the grandchildren relationship, similar to the one for children, but this time extracting the grandparent children ids, and using the IN clause to filter those user rows matching those ids, with their father/mother ids:

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