Active Record: get siblings in directed graph using closure table

For a given record, how may I get all records with which it shares a least one parent? (More about the modeling here.)

# schema
create_table "contents" do |t|
  t.string "title"
end
create_table "content_mappings" do |t|
  t.integer "ancestor_id"
  t.integer "descendant_id"
  t.integer "distance"
end
add_foreign_key "content_mappings", "contents", column: "ancestor_id"
add_foreign_key "content_mappings", "contents", column: "descendant_id"
# model
class Content < ApplicationRecord
    has_many :ancestor_mappings, ->{ joins(:ancestor) },
             class_name: 'ContentMapping', foreign_key: :descendant_id
    has_many :ancestors, through: :ancestor_mappings

    has_many :descendant_mappings, ->{ joins(:descendant) },
             class_name: 'ContentMapping', foreign_key: :ancestor_id
    has_many :descendants, through: :descendant_mappings
end

class ContentMapping < ApplicationRecord
  belongs_to :ancestor, class_name: 'Content'
  belongs_to :descendant, class_name: 'Content'
end

I just don’t seem to be getting the joins correctly. I tried the following:

class Content < ApplicationRecord
  # ...
  def siblings
    Content
      .joins("INNER JOIN content_mappings a on a.descendant_id = #{id} AND a.distance = 1")
      .joins("INNER JOIN content_mappings d on d.ancestor_id = a.ancestor_id AND d.distance = 1")
      .where("contents.id <> #{id}")
  end
end

But the results are incorrect:

a = Content.create!(title: "A")
b = Content.create!(title: "B")
ContentMapping.create!(ancestor: a, descendant: a, distance: 0)
ContentMapping.create!(ancestor: b, descendant: b, distance: 0)
ContentMapping.create!(ancestor: a, descendant: b, distance: 1)
# i.e. A is parent of B
b.siblings.map(&:title) # => returns ["A"] instead of []

I’ve used ancestry in the past for this kind of need.

It maintains paths precomputed in an internal column. Fetching a subtree translates to finding all rows with a certain prefix, pretty simple and performant.

You don’t even need to know the internals, the gem abstracts them behind a tree API.

Thanks @fxn for the tip, however the gem doesn’t support multiple parents which we need. I guess the term “tree” can be misleading (although trees can indeed have many roots …). I answered something similar about the closure_tree gem.

@sdubois Got it. I don’t think nodes in a tree are allowed to have multiple parents, I believe you have a graph.

Good point, I’ve changed “closure tree” to “directed graph with closure table” in the thread title, hopefully it’s clearer.

OK, this seems to work:

class Content < ApplicationRecord
  # ...
  def siblings
    Content
      .joins("JOIN content_mappings s ON s.descendant_id = contents.id")
      .joins("JOIN content_mappings a ON a.ancestor_id = s.ancestor_id")
      .where("a.descendant_id" => id, "a.distance" => 1, "s.distance" => 1)
      .where.not(id:)
  end
end
Load all contents
joined with the contents' ancestors
joined with the ancestors' descendants
where self is among the ancestors' descendants
where the contents' ancestors are direct parents
where the ancestors' descendants are direct children
where the content id is not this one's.

Some ideas to generalize:

  • keep self by dropping where.not(id:)
  • include cousins with distance >= 1
  • order by distance to show siblings then cousins
class Content < ApplicationRecord
  # ...
  def peers(distance: 1, with_self: true)
    Content
      .joins("JOIN content_mappings s ON s.descendant_id = contents.id")
      .joins("JOIN content_mappings a ON a.ancestor_id = s.ancestor_id")
      .where("a.descendant_id" => id)
      .where("a.distance" => 1..distance, "s.distance" => 1..distance)
      .then { |scope| with_self ? scope : scope.where.not(id:) }
      .order("a.distance")
  end
end