MySql Friend of my friends and Friend of my of my Friends of my Friends

Question

i have a problem with mysql query, and can't get it done in any way. I already checked the other topics, but can't get any of the solutions to work in my case.

I have 2 tables, one is users

id, name, email....

and second for friends is named friends :)

user1_id, user2_id

now i need to get from the database all of the user info for friends of my friends, and next level friends, the Friend of my of my Friends of my Friends, those should be 2 separate queries. Of course the search should exclude me, and be distinct.

Try looking [here](http://stackoverflow.com/questions/935098/database-structure-for-tree-data-structure) for how to model / query a Tree structure in SQL. — threenplusone, Dec 16 '12 at 08:18
Without going into detail (because your question is confusing), from what I can tell, you will have to have a loop inside of a loop. — bobthyasian, Dec 16 '12 at 08:22
To shop code, you will have to pay us.. Seriously, give us more detail on what you tried and where you're stuck. You may read this first: http://stackoverflow.com/faq — nawfal, Dec 16 '12 at 08:50

score 0 · Accepted Answer · answered Dec 16 '12 at 11:02

It should be done with a StoredProcedure like this

CREATE PROCEDURE `friend_list`(user_id INT, MaxLevel INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE LLevel, LCount INT DEFAULT 0;

DROP TABLE IF EXISTS tmp_friends;

CREATE TEMPORARY TABLE tmp_friends
    SELECT user1_id AS friend_id, LLevel AS friend_level
        FROM friends
        WHERE user2_id = user_id
    UNION
    SELECT user2_id, LLevel
        FROM friends
        WHERE user1_id = user_id;

SELECT COUNT(*) INTO LCount FROM tmp_friends;

DROP TABLE IF EXISTS tmp_fr;

WHILE ( LCount > 0 ) AND ( LLevel < MaxLevel ) DO

    SET LLevel = LLevel + 1;

    CREATE TEMPORARY TABLE tmp_fr
        SELECT user1_id as friend_id, LLevel as friend_level
        FROM friends
        WHERE user2_id IN ( SELECT friend_id FROM tmp_friends WHERE friend_level = LLevel - 1 );

    INSERT INTO tmp_fr
        SELECT user2_id as friend_id, LLevel as friend_level
        FROM friends
        WHERE user1_id IN ( SELECT friend_id FROM tmp_friends WHERE friend_level = LLevel - 1 );

    DELETE FROM tmp_fr 
    WHERE friend_id IN ( SELECT friend_id FROM tmp_friends ) 
       OR friend_id = user_id;

    SELECT COUNT(*) INTO LCount FROM tmp_fr;

    INSERT INTO tmp_friends
        SELECT * FROM tmp_fr;

    DROP TABLE tmp_fr;

END WHILE;

SELECT * 
FROM tmp_friends 
ORDER BY friend_level;

DROP TABLE tmp_friends;

END

You will get a result set containing the id of a user and the friend level (0 is direct, 1 friend of friend, ...).

You just have to pass the user_id and the maximum friendlevel

CALL friend_list( 1, 10 );

I used this table structure

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `friends` (
  `user1_id` int(11) unsigned NOT NULL,
  `user2_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`user1_id`,`user2_id`),
  KEY `fk_friends_users2` (`user2_id`),
  CONSTRAINT `fk_friends_users2` FOREIGN KEY (`user2_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_friends_users1` FOREIGN KEY (`user1_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Wow, Sir Rufo, this is it! It's way out of my league, i was trying to do this without the temporary table, but what you did is realy incredible, thank you very much :) — user1907522, Dec 16 '12 at 12:16

MySql Friend of my friends and Friend of my of my Friends of my Friends

1 Answers1