Getting through ActiveRecord people linking two given person

I have a Person model with:

has_many :from_relationships, :class_name => "Relationship", :foreign_key => "from_person_id"
has_many :to_relationships, :class_name => "Relationship", :foreign_key => "to_person_id"

and a Relationship model with:

belongs_to :from_person, :class_name => "Person"
belongs_to :to_person, :class_name => "Person"

Given a person p1, I want to implement the instance method p1.people_connecting_to(p2) that returns all the people who indirectly link p1 to the other person p2. For instance if I have the following relationships:

• p1 => p3 => p2
• p1 => p4 => p2
• p1 => p5 => p6 => p2

I want p1.people_connecting_to(p2) to return [p3, p4]. Is it possible to achieve in a single SQL request through ActiveRecord?

Thanks :)

开发者_如何学Python

EDIT:

Thanks Ed, your answer leads me to the following solution. I've added:

has_many :to_relations, :source => :to_person, :through => :from_relationships
has_many :from_relations, :source => :from_person, :through => :to_relationships

and implemented people_connecting_to like this:

def people_connecting_to(other_person)
  to_relations.joins(:from_relationships).where(:"from_relationships_people.to_person_id" => other_person.id)
end

---

You are looking at a fairly complex algorithm. Do a search for breadth-first and depth-first search to get ideas how to implement a recursive method in your Person model to do this.

One general suggestion: set up Person-to-Person associations in your Person model, like this:

  has_many :from_relations, :source => :from_person, :through => :from_relationships
  has_many :to_relations, :source => :to_person, :through => :to_relationships

Then you can get a collection of relations with @person.from_relations and @person.to_relations.

Depending on your application needs, you may be able to simplify things further by dealing with direction in your relationship model, like this:

Person model:

  has_many :relationships
  has_many :relations, :through => :relationships

Relationship model

  belongs_to :person
  belongs_to :relation, :class_name => "Person"

With the more simple assocations, an instance method in your Person model to find if two persons are related would look something like this:

def related_to?(target)
  if self == target
    return true
  elsif self.relations.empty?
    return false
  else
    return self.relations.each {|relation| relation.related_to?(target)}
  end
end

Note it uses recursion. Also, I didn't work through the algorithm to make sure there is no potential for infinite loops due to cyclical associations (Joe -> Bob -> Joe -> Bob ).