Ndtyjky

These are two tables from a part of supposed Twitter like database where users can follow other users. The User.name field is unique.

mysql> select uID, name from User;

+-----+-------------------+

| uID | name              |

+-----+-------------------+

|   1 | Alice             |

|   2 | Bob               |

|   5 | Iron Maiden       |

|   4 | Judas Priest      |

|   6 | Lesser Known Band |

|   3 | Metallica         |

+-----+-------------------+

6 rows in set (0.00 sec)



mysql> select * from Follower;

+-----------+------------+

| subjectID | observerID |

+-----------+------------+

|         3 |          1 |

|         4 |          1 |

|         5 |          1 |

|         6 |          1 |

|         3 |          2 |

|         4 |          2 |

|         5 |          2 |

+-----------+------------+

7 rows in set (0.00 sec)



mysql> call newFollowSuggestionsForName('Bob');

+-------------------+

| name              |

+-------------------+

| Lesser Known Band |

+-------------------+

1 row in set (0.00 sec)

I want to make an operation that will suggest for a user X a list of users they may be interested in following. I thought one heuristic could be to show X for all y who user y follows where X and y follow at least 3 of the same Users. Below is the SQL I came up with to do this. My question is if it could be done more efficiently or nicer in some other ways.

DELIMITER //

CREATE PROCEDURE newFollowSuggestionsForName(IN in_name CHAR(60))

BEGIN



DECLARE xuid INT;

SET xuid = (select uID from User where name=in_name);  



select name

from User, (select subjectID

            from follower

            where observerID in (

                select observerID

                from Follower

                where observerID<>xuid and subjectID in (select subjectID from Follower where observerID=xuid)

                group by observerID

                having count(*)>=3

            )

    ) as T

where uID = T.subjectID and not exists (select * from Follower where subjectID=T.subjectID and observerID=xuid);



END //

DELIMITER ;

Consider the following refactored SQL code (untested without data) for use in stored procedure.

select u.`name`

from `User` u

inner join 

   (select subf.observerID, subf.subjectID

    from follower subf

    where subf.observerID <> xuid

   ) f

on u.UID = f.subjectID 

inner join 

   (select f1.observerID

    from follower f1

    inner join follower f2

      on f1.subjectID = f2.subjectID 

     and f1.observerID <> xuid

     and f2.observerID = xuid

    group by f1.observerID

    having count(*) >= 3

   ) o

on f.observerID = o.observerID 

I think the basic query starts as getting all "observers" who share three "subjects" with a given observer:

select f.observerid

from followers f join

     followers f2

     on f.subjectid = f2.subjectid and

        f2.observerid = 2

group by f.observerid

having count(*) = 3;

The rest of the query is just joining in the names to fit into your paradigm of using names for references rather than ids.