Kelli Shaver

10 months ago

Self-Referencing Many-to-Many Relationships, of a Sort.

Earlier today, I had to create what was essentially a self-referencing many-to-many relationship in Sinatra. I think I may have approached it a bit differently than usual, so I’d like to take a moment to talk about it.

I feel like the code I ended up with isn’t the most pure implementation, and it’s not without its limitations, but it’s pretty quick to implement and doesn’t require a lot of duplicate join data. It works, it’s short, and it makes sense.

In simplified terms, let’s break it down like this:

We have a model called Person, representing people using the application. Each Person can be friends with other people. They can either initiate this friendship themselves, or it can be initiated by someone else (the other friend).

So I created two models.

• Person - The actual person record
• Friendship - This will represent the relationship between two people.

Essentially, Friendship will act as a join table, joining Person to Person in a friend relationship.

Before we create the models, let’s start with our database migrations.

class CreatePeople < ActiveRecord::Migration
  def up
    create_table(:people) do |t|
      t.string :name
      t.timestamps
    end
  end

  def down
    drop_table :people
  end
end

As you can see, the table for people records is very simple. Right now, we’re just capturing names.

The friendship records get a bit more complicated, but they’re still pretty simple.

class CreateFriendships < ActiveRecord::Migration
  def up
    create_table(:friendships) do |t|
      t.integer :created_by_person_id
      t.integer :created_on_person_id
      t.timestamps
    end
    add_index :friendships, :created_by_person_id
    add_index :friendships, :created_on_person_id
  end

  def down
    drop_table :friends
  end
end

What’s going on here? We’re basically just creating a join record between the person creating the friend relationship (Person.created_by_person_id) and the person that is being befriended (Person.created_on_person_id). The names could be a bit better here, but I think they’re good enough - They’re descriptive, and right now, that’s more important than being brief.

Next, let’s move on to the models. Here’s where things get really interesting.

Again, not yet, though. Person is quite simple:

class Person < ActiveRecord::Base
  validates_presence_of :name
end

Friendship, on the other hand, has a bit more going on.

class Friendship < ActiveRecord::Base
  belongs_to :befriending, :class_name => 'Person', :foreign_key => :created_by_person_id
  belongs_to :befriended, :class_name => 'Person', :foreign_key => :created_on_person_id
end

How when we look up a friendship, we can see which person created the friendship, by referenceing Friend.befriending and which person was on the receiving end, via Friend.befriended.

But what about Person.friendships? You’ll notice above that we didn’t define a has_many relationship between Person and Friendship. In fact, it’s the friendship that has the people.

So let’s create a custom method. In fact, let’s just call it friends and skip returning the Friendship join entirely, since it’s rare that we’ll need it.

Let’s go back to our Person class and modify it a bit. I like to do things the old-fashioned way and write my SQL queries from scratch. If you prefer, you can use ActiveRecord methods.

class Person < ActiveRecord::Base
  validates_presence_of :name

  def self.friends
    Friendship.where(
      "id IN (
        SELECT created_on_person_id FROM friendships WHERE created_by_person_id=:id
      ) OR id IN (
        SELECT created_by_person_id FROM friendships WHERE created_on_person_id=:id
      )", { :id => self.id }
    )
  end
end

Now we can pull up everyone we’re friends with by calling Person.friends.

It might also be handy to be able to find individual friendships between two people.

def self.friendship(person)
   Friendship.where(
    "created_by_person_id=:id OR created_on_person_id=:id",
    { :id => person.id }
   )
end

Now we can check to see if a friendship record exists between two friends via Person.friendship(person).

Sub-queries like the ones used in Person.friends can get pretty database-intensive. If your users will have several hundred friends, this isn’t the most practical approach. You would either want to model a more sophisticated friend relationship.

In my case, it’s unlikely that the person will have more than a few dozen friends.