Getting 5level relation with one mysql query

Question

Hi folks.Thank u a lot for your answers beforehand. I need to make a such thing

I have a table friendship (id,user_id,friend_id,status,timestamp)

So lets say I am a user with user_id=43 and I am visiting a user with user_id=15

In the profile it should be a connection line of friendships

Let me describe ... lets say I have a friendship with user (user_id=3 and the user with user_id=3 is friend with user which profile I am visiting.

So on web site I will see

Connection

MyIcon->UserIcon(15)->UserIcon(3)->UserIcon(i am visiting)

And only in case when the friendship statuses for all are status=1...

Can anybody tell me how the query should look like?

Answer 1

You might look at this answer to a question about recursive selection to see a hack you can do on the mySql side of things. It shows how to create a hierarchy for selection.

In mySql (ANSI SQL), there is no "native" way to perform such a query.

Answer 2

With plain MySQL, there is no native way to do this. You have to either decide how deep you want to look, and use that amount of JOIN operations to see if you can 'reach' from one user id to the other, or you could give the community contributed Graph engine a whirl:

http://openquery.com/products/graph-engine

(this involves using a non-official binary AFAIK, perhaps it is already availble as a plug-in, but I am not sure aobut that)

With that engine, you can do it in a single simple query:

SELECT * FROM foo WHERE latch = 1 AND origid = 15 AND destid = 43;

And this would then return one row for each link you have to travel to reach from user 15 to user 43. You'd use the application code to display it nicely.

Answer 3

Had you modeled this as a Nested Set modeled hierarchy instead of the Adjacency List model which you have then this query would be trivial. As it is, you're looking at having to use recursion, which isn't natural to a relational database.

For some great information on modeling hierarchies, check out Joe Celko's book.