How to get the social map of friends by a single query from a social networking sql database?

I have a social networking site built on top of the Elgg (php + mysql) framework. My objective is to get all friends of a given user, and also the friend relationships among those friends.

All the information I need is in two tables:

• the "users" table where users are identified by a unique id called guid
• and the "relationships" table where friend relationships are represented by (guid_one, "friend", guid_two) triplets

Friend relationships in Elgg can both be one way or bidirectional, it is more like Twitter's "follow" relationship. Uniqueness of the relationship triplets is guaranteed.

Short example: Considering (1, "Joe"), (2, "Jack") (3, "Jim") users and the following relationships (1, "friend", 2), (2, "friend", 1), (1, "friend", 3), (2, "friend", 3), this could be interpreted as

1. Joe and Jack are mutual friends (follow each other)
2. Jim is followed by both Joe and Jack

What I'd like to get is

• a list of all relationship among the friends for any given user
• in the descending order of the number of relationships (i.e. list relationships first for those friends who follow most of my friends)
• preferably in a single query

What would be the most efficient way to do this?

EDIT So far I have this:

SELECT
    u1.guid, u1.name, u2.guid, u2.name
FROM
    users u1
INNER JOIN relationships r1 ON 
  (u1.guid = r1.guid_one AND r1.relationship = "friend")
INNER JOIN users u2 ON (r1.guid开发者_StackOverflow社区_two = u2.guid)
INNER JOIN relationships r2 ON 
  ((r2.guid_one = xxx AND r2.guid_two = u1.guid) 
  OR (r2.guid_two = xxx AND r2.guid_one = u1.guid))
INNER JOIN relationships r3 ON 
  ((r3.guid_one = xxx AND r3.guid_two = u2.guid) 
  OR (r3.guid_two = xxx AND r3.guid_one = u2.guid))

Where xxx stands for the user's guid I'm interested in. There are two main problems with this: it's not ordered by the number of relationships and it's painfully slow because of the many joins. Also it gets only one way relationships (who is following whom among my friends) - however that could by solved by a union, I think.

Any ideas to improve on this?

You can do a BFS on a stored procedure. initialize the table with the user given, and each step of the BFS will insert into this table the friends of the users in this table. distance (or hops) can be a parameters to this procedure.

Edit: How BFS works (wikipedia). How stored procedures work (mysql), loops and recursion (mysql), and stackoverflow