Rails migrations: many-to-many relationship between the same class | Interpersonal Relationships

Question

I want to connect two People with the help of Relationship.

Person:
* id
* name

Relationship:
* person_a_id
* person_b_id
* connection # an enum with values like "colleagues", "friends", "siblings", etc

I want to accomplish a couple of things:

1. I have created 2 people. I want the second person to be included when I query for Person.first.people and vice versa! (i.e the first person shall be included when I query for Person.second.people). I was close to achieve this with has_and_belongs_to_many :(people|reversed_people): https://stackoverflow.com/a/46230787/6030239

2. Relationship which connects the two has a connection value of friends. I want to create a has_many :friends method, such that the second person will appear in Person.first.friends query and vice versa!

Answer 1

First off - I wouldn't use HABTM here, but instead a named join table (called Relationship) with it's own model and use has_many :people, through: :relationships.

1. I have created 2 people. I want the second person to be included when I query for Person.first.people and vice versa! (i.e the first person shall be included when I query for Person.second.people). I was close to achieve this with has_and_belongs_to_many :(people|reversed_people): https://stackoverflow.com/a/46230787/6030239

You can achieve this with a single activerecord relationship (either has-many through or HABTM) by adding two rows for each relationship (one in each direction). For example:

def add_bi_directional_relationship(first:, second:, type:)
  Relation.create!(person_a: first, person_b: second, connection: type)
  Relation.create!(person_a: second, person_b: first, connection: type)
end

# first.people => [second]
# second.people => [first]

Activerecord associations are designed to query a table by a foreign key, so to use them in a straightforward way you need a table where the value you want to query is going to be in a single column.

However, why do you need it to be done via an activerecord association? You can write a method that does the query you need.

class Person
  has_many :forward_people, through: :relations, #...
  has_many :reversed_people, through: :relations, #...
  def people
    forward_people + reversed_people
    # or build the joins in SQL strings or Arel
  end
end

Or lots of other potential solutions, depending on your needs.

2. Relationship which connects the two has a connection value of friends. I want to create a has_many :friends method, such that the second person will appear in Person.first.friends query and vice versa!

Not sure I totally understand this... but if you take the first approach of adding a relationship for each direction, then you can write a method like:

def friends
  people.where(relations: {connection: 'friends'})
end

You can also do this with another activerecord relationship... but I wouldn't recommend defining multiple activerecord associations for the same foreign keys. An alternative approach could be to define a .friends scope that would allow you to do Person.first.people.friends.

In general these goals are difficult to accomplish because you're specifying an implementation (an activerecord association that returns specific values) without indicating why you need it to be done like / what problems you're trying to solve with it. Activerecord associations are helpful to a point, but they also introduce a layer of abstraction that can add complexity/confusion. Discussing the actual business/application needs you want to solve (e.g. a report that shows X, a view that renders X, or a form that saves X) would allow people to suggest alternative approaches that might be a better fit what you're trying to accomplish.

Answer 2

Since you can't define assocations in ActiveRecord where a the foreign key is one of two columns you can set this up by adding an additional join table:

class Person
  has_many :personal_relationships
  has_many :relationships, through: :personal_relationships
  has_many :related_people, through: :relationships,
                            source: :persons
end

class Relationship
  has_many :personal_relationships
  has_many :persons, through: :personal_relationships
end

class PersonalRelationship
  belongs_to :person
  belongs_to :relationship
  validates_uniqueness_of :person_id, scope: :relationship_id
end

has_and_belongs_to_many is not relevant here. It really only handles the most trivial cases and falls flat on its face if there is even the vaguest hint of complexity.

You would create a relationship between two users with:

@relationship = Relationship.new
@relationship.persons << user_1
@relationship.persons << user_2

This creates two rows in the personal_relationships table.

While this might seem kind of wonky it gives you a single association that can be properly eager loaded and treated as a homogenous collection. The con is that you need more database rows and you'll need to use some more advanced techniques like custom constraints if you want to ensure that a relationship can only have to people on the database level.