vertigoelectric vertigoelectric - 1 year ago 64
SQL Question

How to model Friendship relationships

I have been trying to figure out how to do this, and even with looking at other examples, I can't get it figured out, so maybe I can get some personalized help.

I've got two tables,


In the
table I have a field
, and several others.
In the
table, I have the fields
, and

Basically what I want to do is get all of the status posts by the current user AND those who are friends of the current user (which I can specify in my PHP using a $userid variable).

Here's an example of the
table structure. When a friend request is sent, the userid of the sender and receiver are placed in the table, with a friendship_status of 0. When the request is accepted, the friendship_status is set to 1 and those two are now friends.

friendship_id request_from request_to friendship_status
1 111248 111249 1
2 111209 111249 1
3 111209 111248 0
11 111209 111259 1
5 111252 111209 1
12 111261 111209 1

I realize this may not even be the best structure for determining friendships, especially since the site is relationship based and having to check for friendship connections will be a frequently used thing.

Would it perhaps be better to have two separate tables for
? If so, how would I structure/manage the

Answer Source

You can use a table join (e.g. to find all of the requests.

Actually you can use a subquery here:

SELECT * FROM users_status WHERE userid = "$userid" 
    OR userid in (SELECT request_to   FROM friendships where request_from = "$userid" AND friendship_status = 1)
    OR userid in (SELECT request_from FROM friendships where request_to   = "$userid" AND friendship_status = 1)

replace $userid with your user id

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download