bi-directional friends table

[jacko310592]

hey guys, at the moment i have two tables, one for user details, one for users friends, set out as follows:

 

user_details:

user_id   |   marked_online
------------------------------
001       |   1   
002       |   0   
003       |   1       



user_friends:

user_id_[link1]   |   user_id_[link2]
-----------------------------------------
001               |   003

 

so with the above example, "001" is friends with "003", vice versa.

 

my problem is i need to get all online friends for the logged in user, but im not sure how to alter my code (below) so that the logged in user could be in column "user_id_[link1]" or "user_id_[link2]"

mysql_query("		
SELECT SQL_CALC_FOUND_ROWS *
FROM user_friends AS uf
CROSS JOIN user_details AS ud
ON ud.user_id = uf.user_id_[link2]
WHERE uf.user_id_[link1]='$loggedInUserId'
&& ud.marked_online=TRUE			
");

$loggedInUserId = user_id of signed in user

 

so i need my query to take the user id from either "user_id_[link1]" or "user_id_[link2]" (depending on which column the signed in user appears in) and use it to get the friends details from the user_details table

 

 

hope ive explained my problem well

thanks guys

[DavidAM]

My first thought was something like this:

SELECT user_id, marked_online
FROM user_details JOIN (SELECT IF (user_id_[link1]=$loggedInUserId, user_id_[link2], user_id_[link1]) AS FriendID 
FROM user_friends 
WHERE $loggedInUserId IN (user_id_[link1], [user_id_[link2])) AS UserFriends
ON user_details.user_id = UserFriends.FriendID
WHERE marked_online = TRUE
ORDER BY ... LIMIT ... 

but I don't think the inner SELECT will use any indexes, so it will not scale.

 

Then I thought about using a UNION:

(SELECT SQL_CALC_FOUND_ROWS user_id, marked_online
FROM user_details JOIN user_friends ON user_id = user_id_[link1]
WHERE user_id_[link2] = $loggedInUserID
AND marked_online = TRUE)
UNION ALL
(SELECT user_id, marked_online
FROM user_details JOIN user_friends ON user_id = user_id_[link2]
WHERE user_id_[link1] = $loggedInUserID
AND marked_online = TRUE)
ORDER BY ... LIMIT ... 

 

or even combining the two:

SELECT SQL_CALC_FOUND_ROWS user_id, marked_online
FROM user_details JOIN (SELECT user_id_[link1] AS FriendID FROM user_friends 
WHERE user_id_[link2] = $loggedInUserID
UNION
SELECT user_id_[link2] FROM user_friends 
WHERE user_id_[link1] = $loggedInUserID) AS UserFriends ON user_id = FriendID
WHERE marked_online = TRUE
ORDER BY ... LIMIT ... 

 

I think either of the last two will use indexes on the user_friends table so performance will not degrade so badly as the table grows.  Note: there should -probably- be a separate index on each of the two columns.

 

Actually, as I think about it, I think the middle one will scale best.

[jacko310592]

thanks a billion for the reply DavidAM, the middle one seems to be working just as i needed it to (:

..had to remove the square brackets from my column names though, it seemed to be throwing an error just for using them