Setting up a self join with Rails & ActiveRecord

What is self join?

When you design database models for your application, there are times when a model has to have a relation to itself. To solve this problem, you can refer a single model to different roles and now this model becomes a self join model(self join table in the database).

For example, when we use a social media app you can be a follower who follows other users or you can be a followee who is followed by other users. In this case, our self join model would be a ‘User’. With this self join, you will be able to save all the information of users under this single database model ‘User’, but it would be still possible to track the relationships between followers and followees.

Example of Self join model — User

Let’s look at the example below:

User model has two roles to play in this case, which are Creator and Audience.

User as a Creator:

1. creator can make many lists and add many items to each list
2. items on the creator’s list will have many comments from audiences
3. creator’s list will be followed by many audiences

User as a Audience:

1. audience can leave comments on creators’ items
2. audience can follow many different lists of creators

Active Record Associations setup

Now, let’s look at the code setting up the Active Record associations for these models. The ones you have to look carefully are marked in bold text.

User

## As a creator
has_many :lists, foreign_key: "creator_id"
has_many :items, through: :lists, foreign_key: "creator_id"
has_many :follows, through: :lists, foreign_key: "creator_id"
has_many :audiences, through: :follows, class_name: "User"
## As a audience
has_many :comments, foreign_key: "audience_id"
has_many :loves, class_name: "Follow", foreign_key: "audience_id"
has_many :love_lists, through: :loves, source: :list
has_many :creators, through: :love_lists, foreign_key: "creator_id"

There are ‘loves’ and ‘love_lists’ above in the User model and you might wonder where these came from. Since there are two paths to retrieve user’s follows and lists now because our user can be a creator or audience, we have to refer follow and list models to two different names as well.

I chose to refer follows and lists as ‘loves’ and ‘love_lists’ for the audience user. So now if you want to retrieve your user’s follows that he or she made as an audience, you can call ‘user.loves’ to retrieve the data.

You will also have to set up your associations in models and columns in database tables with using creator and audience, not user.

List

belongs_to :creator, class_name: "User", optional: true
has_many :follows, dependent: :destroy
has_many :audiences, through: :follows
has_many :items, dependent: :destroy
has_many :comments, through: :items

Item

belongs_to :list
has_many :comments, dependent: :destroy
has_many :audiences, through: :comments

Follow

belongs_to :audience, class_name: "User"
belongs_to :list

Comment

belongs_to :audience, class_name: "User"
belongs_to :item

Database tables setup

The schema of database tables looks as below:

create_table "users", force: :cascade do |t|
   t.string "name"
   t.string "password_digest"
   t.string "fullname"
   t.integer "age"
   t.string "gender"
end
create_table "follows", force: :cascade do |t|
   t.integer "audience_id"
   t.integer "list_id"
end
create_table "items", force: :cascade do |t|
   t.string "content"
   t.string "status"
   t.string "category"
   t.integer "list_id"
end
create_table "lists", force: :cascade do |t|
   t.string "title"
   t.integer "creator_id"
end
create_table "comments", force: :cascade do |t|
   t.string "content"
   t.integer "audience_id"
   t.integer "item_id"
end

I hope this blog helped your understanding of self join, it is definitely not an easy concept to grasp quickly. However, it is such a practical and fun way to design your models. I hope you will find it as an enjoyable practice as well! :)