3

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, users_status and friendships.

In the users_status table I have a field userid, and several others. In the friendships table, I have the fields request_to,request_from, and friendship_status.

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 friendships 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 friend_requests and friendships? If so, how would I structure/manage the friendships table?

Tunaki
  • 132,869
  • 46
  • 340
  • 423
vertigoelectric
  • 1,307
  • 4
  • 17
  • 37

2 Answers2

4

You can use a table join (e.g. http://dev.mysql.com/doc/refman/5.0/en/join.html) 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

Foo Bah
  • 25,660
  • 5
  • 55
  • 79
  • I realize that JOIN is an option, and I tried it, but I don't know how to get it to work with my specific situation. That's why I posted the question. I need more personalized help with this one. – vertigoelectric Oct 06 '11 at 02:18
  • @vertigoelectric actually you don;t need a join -- a subquery will suffice. I updated my response – Foo Bah Oct 06 '11 at 02:23
  • If a user requests the current user his friendships, and the current user accepts, then this query won't work right because that user won't be returned as a friend. Am I correct? – Mosty Mostacho Oct 06 '11 at 02:40
  • @MostyMostacho yes you would need to check the other case as well, which is a similar subquery. updating response – Foo Bah Oct 06 '11 at 02:42
  • @Foo Bah, I did try a subquery, too. I tried your example, but it doesn't seem to properly check to see if the userid is in the request_to OR request_from. I tested it as userA who sent a request to userB, who accepted it. userA can see status posts from himself and the friend, but userB cannot see status posts from userA, who sent him the friend request even though they are both friends. I swapped `request_to` and `request_from` in your example, and then userA could no longer see posts by userB, an expected reversal (but userB could still see both, which was strange). – vertigoelectric Oct 06 '11 at 02:49
  • @MostyMostacho, looks like you made the point more quickly than I could. I look forward to Foo Bah's updated example. – vertigoelectric Oct 06 '11 at 02:51
  • @vertigoelectric you need two subqueries -- check my response – Foo Bah Oct 06 '11 at 02:53
  • @FooBah, it works perfectly! Thank you so much! Using two subqueries in a linear fashion like that should have been obvious. Thanks! – vertigoelectric Oct 06 '11 at 14:17
  • @FooBah, would you mind one more thing? I have another function that gets the same data but only if it is newer than what's already showing on the page. I need to add `statuspostdate > '$latestposttimestamp'` to the whole thing. I've tried different ways of doing it but nothing is working so far. I'll keep trying and let you know if I figure it out, but just in case, would you mind helping me with it? – vertigoelectric Oct 06 '11 at 14:54
  • @vertigoelectric can you ask a separate question, and in that question put the code for the function – Foo Bah Oct 06 '11 at 14:57
  • @FooBah, **UPDATE** Never mind. I'm an idiot. I forgot to define `$userid` on that specific page! No wonder nothing was coming up. I got it working now! – vertigoelectric Oct 06 '11 at 15:05
3

The simplest schema I can think of is:

PENDING_FRIENDSHIPS(request_from, request_to)
FRIENDSHIPS(request_from, request_to)

I also removed the ID because both fields on both tables will be compound primary keys (request_from, request_to).

To get all friends from the current user just run:

select * from friendships
where $currentUser = request_from OR $currentUser = request_to

This would return both columns and you would have to remove in PHP the current user.

Another way to get all friends from this schema is to run a UNION:

select request_from from friendships
where request_to = $currentUser
UNION
select request_to from friendships
where request_from = $currentUser

The drawback of this solution is that you're running 2 selects

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123