Getting random friend of friend with mutual count

Question

I have a user and a friend table. User having user details and friend has friends of users. Here is the schema of user table..

create table smsusers(
  id varchar(60),
  password varchar(50) not null,
  fname varchar(30) not null,
  lname varchar(30),
  mailid varchar(50) not null,
  gender varchar(10) not null,
  primary key(id)
);

and friend table..

create table friends_list(
  friend_of varchar(60) NOT NULL,
  friends_id varchar(60) NOT NULL,
  friendship_status varchar(30),
  friendship_date timestamp NOT NULL,
  primary key(friend_of,friends_id),
  foreign key(friend_of) references smsusers(id)
  ON DELETE CASCADE ON UPDATE CASCADE);

Profile pic

create table profile_pic(pic_id varchar(200),profile_pic_path varchar(1000),small_pic_path varchar(1000),
adddate varchar(100),userid varchar(60),foreign key(userid) references smsusers(id) ON DELETE CASCADE ON UPDATE CASCADE,
primary key(pic_id));

Here I want to fetch random 5 friend of friend with mutual friend count

I tried this query

SELECT DISTINCT ff.friends_id,
   concat(u.fname,' ',u.lname),
   pp.small_pic_path,
   count(*) AS mutual_friend_count
    FROM
    friends_list f
    JOIN friends_list ff
    ON ff.friend_of =f.friends_id
    LEFT JOIN smsusers u
    ON
    u.id=ff.friends_id
    LEFT JOIN profile_pic pp
    ON
    ff.friends_id=pp.userid
    WHERE f.friend_of = 1
    AND ff.friends_id NOT IN (1)
GROUP BY friends_id

This query showing friend of friend and also my friends, and mutual count is also not correct. Query that I tried.

Answer 1

RAND() function in MySQL returns a random floating-point value and you can also retrieve rows in random order like this:

   SELECT * 
     FROM tbl_name 
 ORDER BY RAND();

So re-writing your query to show the friend's of each user and their count, randomly chosen:

   SELECT DISTINCT u.id,
          CONCAT(u.fname,' ',u.lname) AS 'User',
          GROUP_CONCAT(CONCAT(f.fname,' ',f.lname) SEPARATOR ', ') AS 'Friends',
          COUNT(*) AS mutual_friend_count
     FROM smsusers u
     JOIN friends_list ff
       ON u.id = ff.friends_id
     JOIN smsusers f
       ON ff.friend_of = f.id
  --WHERE u.id = 1
 GROUP BY friends_id
 ORDER BY RAND()
    LIMIT 5;

If you use the WHERE clause, the query would output the friend's for user Manish Sahu.

EDIT:

Another way to accomplish it is to create a VIEW:

   CREATE VIEW facebook AS
   SELECT u.id,
          CONCAT(u.fname,' ',u.lname) AS 'User',
          ff.friend_of
     FROM smsusers u
     JOIN friends_list ff
       ON u.id = ff.friends_id
     JOIN smsusers f
       ON ff.friend_of = f.id;

Then, re-writing the first query using our newly generated VIEW:

   SELECT f.*,
          GROUP_CONCAT(friend_of SEPARATOR ', ') AS 'FriendsIDs',
          COUNT(*) AS mutual_friend_count
     FROM facebook f
 GROUP BY id
 ORDER BY RAND()
    LIMIT 5;

Then, you could also retrieve a list of randomly generated user's friends:

   SELECT f.*,
          (SELECT `User` 
             FROM `facebook` 
            WHERE `friend_of` IN (1)
         ORDER BY RAND()
            LIMIT 1) AS 'RandomFriends'
     FROM facebook f
    WHERE id = 1;

Additionally, the function GROUP_CONCAT is quite useful in debugging such complicated queries.

More info:

• MySQL Reference Manual: RAND() function
• StackOverflow: How to use GROUP BY to concatenate strings in MySQL?