twitter-style follower/following/friend sql query

Question

I'm working on a twitter type of following system. I'm joining two tables, users and followers to get the first and lastname of users who are in the followers table. Then I'm running an inner join on the followers table to capture follower and friend relationships. I'm displaying the results as followers (who follows you), following (who you follow), and friends (mutual following).

With the query below, I'm only able to show the name of the user who wants to see their friends. I'd like to show the FRIENDS of the user, not the user's own name, but can't figure out how to get the users table to do double duty--that is, show me the name of the user and the name of their friend, or just the friend's name.

Thanks.

SELECT users.id, users.firstname, users.lastname, followers.follower_user_id, followers.followee_user_id
            FROM users
            JOIN followers ON followers.follower_user_id = users.id
            INNER JOIN followers ff ON followers.followee_user_id = ff.follower_user_id AND followers.follower_user_id = ff.followee_user_id

Answer 1

I believe that your schema requires a union table to assemble the information you need; and it may be more efficient to do this in multiple tables. To maintain a separate table of followers with (possible) duplicate information from users may also be undesireable. A more efficient schema would be:

mysql> select * from users;
+-----+------------+---------+
| uid | fname      | lname   |
+-----+------------+---------+
|   1 | Phillip    | Jackson |
|   2 | Another    | Name    |
|   3 | Some Crazy | User    |
|   4 | Nameless   | Person  |
+-----+------------+---------+
4 rows in set (0.00 sec)


mysql> select * from follows;
+---------+-----------+
| user_id | follow_id |
+---------+-----------+
|       1 |         4 |
|       2 |         3 |
|       3 |         2 |
|       4 |         2 |
+---------+-----------+
4 rows in set (0.00 sec)

And then your query would look like:

select users.uid,
users.fname,
users.lname,
u.uid,
u.fname,
u.lname from users
inner join follows f on (f.user_id=users.uid)
inner join users u on (u.uid=f.follow_id)

Which returns:

mysql> select users.uid,
    -> users.fname,
    -> users.lname,
    -> u.uid,
    -> u.fname,
    -> u.lname from users
    -> inner join follows f on (f.user_id=users.uid)
    -> inner join users u on (u.uid=f.follow_id);
+-----+------------+---------+-----+------------+--------+
| uid | fname      | lname   | uid | fname      | lname  |
+-----+------------+---------+-----+------------+--------+
|   1 | Phillip    | Jackson |   4 | Nameless   | Person |
|   4 | Nameless   | Person  |   2 | Another    | Name   |
|   2 | Another    | Name    |   3 | Some Crazy | User   |
|   3 | Some Crazy | User    |   2 | Another    | Name   |
+-----+------------+---------+-----+------------+--------+
4 rows in set (0.00 sec)

Answer 2

SELECT  u.id, u.first_name, u.last_name, uf.id, uf.first_name, uf.last_name
FROM    users u
JOIN    followers f
ON      f.follower_user_id = u.id
JOIN    followers ff
ON      (ff.followee_user_id, ff.follower_user_id) = (f.follower_user_id, f.followee_user_id)
JOIN    users uf
ON      uf.id = f.followee_user_id