Simply skip the first paragraph if you want to see the implementation immediately. The first paragraph is an event that led to writing this article 😂

Not quite long ago, I was laid off from my previous workplace. The company was creating a social application however, I used MongoDB for the database which was a terrible choice. But I had the choice then because MongoDB was the only database that I knew about 😂. When the tough hour (building the recommendation system) came, they had no other option but to sack me.

I loved the experience of working there though

So straight to the point.

But before we jump into the query, I want us to understand how the recommendation works.

1. The system is like a tree with a different depth ranging from 1 to infinity but in the post, we will use a depth of 3 In the first depth, we pick random users to use as the node. From this node, we return their followers.

2. In the second depth, we simply get followers of users from our first depth result.

3. In the third depth, we also get followers of users from our second depth result.

This is a simple explanation of what we want to implement. By default, MongoDB was not a good option for this type of relationship in social applications.

To achieve this, we will use the Recursive Common Table Expression. The concept here is to be able to iterate through the followers like a tree.

Let us create a table two tables “users” and “followers” and insert values into them,

-- Create a table called users

create table users(
    id serial primary key,
    username varchar(40)
);


--- Insert some values into the table

insert into users(username)
values('Johnny Cash'),
('Taylor Swift'),
('Sound Sultan'),
('Tuface'),
('Burna Boy');

--- Create a table called followers

CREATE TABLE followers (
    id SERIAL PRIMARY KEY,
    leader_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    follower_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(leader_id, follower_id)
);

--- Insert values into this 
insert into followers(leader_id, follower_id)

values(4, 1),
(3, 4),
(5, 1),
(2, 5),
(1, 2),
(5, 3)

We will select the first user as our node which is Country music legend “Johnny Cash”. We will then use a Recursive Common Table Expression to iterate through the followers of Johnny Cash

with recursive suggestion(leader_id, follower_id, depth) as (
    select leader_id, follower_id, 1 as depth from followers
    where follower_id = 1 --- This is the value of the particular user which you want to populate the result from (our Node)

    union

    select followers.leader_id, followers.follower_id, depth + 1 from followers
    join suggestion on suggestion.leader_id = followers.follower_id
    where depth < 3
)

select distinct users.id, users.username from suggestion 
join users on users.id = suggestion.leader_id

Below is a graphical representation of how Recursive Common Table Expression works

In the diagram, I tried to explain this using a flow chart. Below is a sample result. The result is recommended users based on followers of Johnny Cash

\===================

| "id" | "username" |

\===================

| 4 | "Tuface" |

\===================

| 3 | "Sound Sultan" |

\===================

| 2 | "Taylor Swift" |

\===================

| 5 | "Burna Boy" |

\===================

I was able to understand the concept from Stephen Grider’s course on PostgreSQL

If you have any questions, feel free to leave a comment and I will respond as soon as possible.