Sign Up Login Videos
1

Select Mutual Followers

1

Select Mutual Followers

Hi everyone, this is mostly directed to Francis.

I followed the social network tutorial and now require further help.

I posted my question on Stackoverflow so let me copy it here.

I'm working on the user list page, In my database are two tables, users & followers.

The id column in the users table is a foreign key in the followers table whose columns are id, user_id & follower_id.

The follower_id is the user id of the user who followed the user_id user.

When showing the list of all users on the social network I want to exclude:

  1. The users being followed by the logged_in_user
  2. The users following the logged_in_user

When showing the list of all users I also want to prioritize any mutual followers to be displayed first.

This are my current queries ($userid refers to the logged_in_user and $start is an integer sent when the user loads more users)

<?php
// List of users following the logged_in_user
$followers = $db->query(
        'SELECT followers.id, followers.user_id, followers.follower_id, users.id, users.username, users.profileimg 
        FROM followers, users 
        WHERE followers.user_id=:userid 
        AND followers.follower_id=users.id 
        LIMIT 8 OFFSET '.$start.';', array(':userid'=>$userid));

// List of users being followed by the logged_in_user
$following = $db->query(
        'SELECT followers.id, followers.user_id, followers.follower_id, users.id, users.username, users.profileimg 
        FROM followers, users 
        WHERE followers.follower_id=:userid 
        AND followers.user_id=users.id 
        LIMIT 8 OFFSET '.$start.';', array(':userid'=>$userid));

// List of all users: here I excluded the logged_in_user
$users = $db->query(
        'SELECT users.id, users.username, users.profileimg, users.gender, users.last_seen 
        FROM users 
        WHERE users.id!=:userid 
        LIMIT 8 OFFSET '.$start.';', array(':userid'=>$userid));

Could anyone help me tie these three queries together to get the desired result:

A list of all users who are following/being followed by the users who are following/being followed by the logged in user i.e mutual followers.

https://stackoverflow.com/questions/51932941/select-mutual-followers

  • PHP
  • SQL
  • Social Network

2 Replies

Here's my example query for determining mutual followers between two users. It involves 3 joins so it probably won't scale too well if you have thousands of followers, but here it is anyway.

SELECT users.username FROM users,
(SELECT follower_id FROM followers WHERE user_id = 1) AS table1, 
(SELECT follower_id FROM followers WHERE user_id = 2) AS table2
WHERE table1.follower_id = table2.follower_id
AND (users.id = table1.follower_id OR users.id = table2.follower_id);

What it does is query the followers table twice, once for the first user and once for the second user. The resulting tables are stored in table1 and table2 respectively.

Then we query the users table and join the result to the two tables we just created.

In the example query, I used 1 and 2 for the user ids.

This gives us the result we want. We just have to supply the ids of the two users whose followers we want to compare.

Below I've also included the example schema I used to test my query. It creates the database, tables and inserts some test data for you.

CREATE DATABASE mydb;
USE mydb;

CREATE TABLE users(
    id INT,
    username VARCHAR(20),
    PRIMARY KEY (id)
);
CREATE TABLE followers(
    user_id INT,
    follower_id INT,
    FOREIGN KEY (follower_id) REFERENCES users(id)
);

INSERT INTO users VALUES (1, 'Francis');
INSERT INTO users VALUES (2, 'Conor');
INSERT INTO users VALUES (3, 'Liam');
INSERT INTO users VALUES (4, 'Jack');

INSERT INTO followers VALUES (1, 2);
INSERT INTO followers VALUES (1, 3);
INSERT INTO followers VALUES (1, 4);
INSERT INTO followers VALUES (2, 1);
INSERT INTO followers VALUES (2, 4);
INSERT INTO followers VALUES (4, 1);
INSERT INTO followers VALUES (2, 3);
≪ Show more ≫
2

Here's my example query for determining mutual followers between two users. It involves 3 joins so it probably won't scale too well if you have thousands of followers, but here it is anyway.

SELECT users.username FROM users,
(SELECT follower_id FROM followers WHERE user_id = 1) AS table1, 
(SELECT follower_id FROM followers WHERE user_id = 2) AS table2
WHERE table1.follower_id = table2.follower_id
AND (users.id = table1.follower_id OR users.id = table2.follower_id);

What it does is query the followers table twice, once for the first user and once for the second user. The resulting tables are stored in table1 and table2 respectively.

Then we query the users table and join the result to the two tables we just created.

In the example query, I used 1 and 2 for the user ids.

This gives us the result we want. We just have to supply the ids of the two users whose followers we want to compare.

Below I've also included the example schema I used to test my query. It creates the database, tables and inserts some test data for you.

CREATE DATABASE mydb;
USE mydb;

CREATE TABLE users(
    id INT,
    username VARCHAR(20),
    PRIMARY KEY (id)
);
CREATE TABLE followers(
    user_id INT,
    follower_id INT,
    FOREIGN KEY (follower_id) REFERENCES users(id)
);

INSERT INTO users VALUES (1, 'Francis');
INSERT INTO users VALUES (2, 'Conor');
INSERT INTO users VALUES (3, 'Liam');
INSERT INTO users VALUES (4, 'Jack');

INSERT INTO followers VALUES (1, 2);
INSERT INTO followers VALUES (1, 3);
INSERT INTO followers VALUES (1, 4);
INSERT INTO followers VALUES (2, 1);
INSERT INTO followers VALUES (2, 4);
INSERT INTO followers VALUES (4, 1);
INSERT INTO followers VALUES (2, 3);
≪ Show more ≫
1

Thanks Francis this helps a lot!! Thanks for the tutorial too... Hoping you can take a look at my code once I'm done building my social network purely based on your code 😁

1

Thanks Francis this helps a lot!! Thanks for the tutorial too... Hoping you can take a look at my code once I'm done building my social network purely based on your code 😁

x