CODEWITHSUNDEEP
mysqlsqldatabase
Dylan Cross
Dylan Cross

Reputation: 5986

Simplify MySQL query

I have a MySQL query that I used to use to return mutual friends between two users, but now that I am recoding my website, I am trying to simplify this code, or at least make it better.

So here's my code below to check mutual friends:

SELECT a.friendID
FROM
(SELECT CASE WHEN userID = $id
    THEN userID2 
    ELSE userID 
    END AS friendID 
FROM friends 
WHERE (userID = $id OR userID2 = $id) 
    AND state='1'
) a

 JOIN
(SELECT CASE WHEN userID = $session
    THEN userID2 
    ELSE userID 
    END AS friendID 
FROM friends 
WHERE (userID = $session OR userID2 = $session) 
    AND state='1'
) b

ON b.friendID = a.friendID

My table is set up like this:

   userID -- userID2 -- state
      1 ------- 2 ------- 1
      2 ------- 3 ------- 1
      1 ------- 3 ------- 0

(sorry, I don't know how to do the pretty database structure design, so if someone could edit that for me...)

but for the above, when user 1 is on user 3's profile, since user 1 and user 2 are friends, and user 2 and user 3 are friends, but user 1 and user 3 are not, it should return user 2 as a mutual friend. (state 1 means friendship accepted, state 0 means friendship pending, so only if state is 1 should it be counted as a friend)

Also note that userID and userID2 can be in any order, it depends on who requests who as a friend, so like the above query does, I need to also have the "friendID" returned, as the above query does right.

Upvotes: 2

Views: 1163

Answers (4)

Sparkup
Sparkup

Reputation: 3754

You could do :

SELECT userID2
FROM friends_table
WHERE userID IN ( $id, $session )
AND state = 1
GROUP BY userID2
HAVING COUNT(userID2) >= 2

friends_table :

user_id | friend_id | state
-----------------------------
   1    |     7     |    1
   2    |     3     |    0
   7    |     1     |    1

User 1 and 7 are friends, user 2 wants to be friends with user 3, but user 3 hasn't responded yet.

Upvotes: 0

Laurence
Laurence

Reputation: 10976

Not necessarily the fastest but simple to read:

Create View ActiveFriends As
Select
  UserId, UserID2
From
  friends
Where
  State = '1'
Union All
Select
  UserId2, UserID
From
  friends
Where
  State = '1'


Select
  f1.userID2 as MutualFriendId
From
  ActiveFriends f1
    Inner Join
  ActiveFriends f2
     On f1.UserID2 = f2.UserID
Where
  f1.UserID = $session And
  f2.UserID2 = $id

http://sqlfiddle.com/#!2/5748f/1/0

Upvotes: 1

Hussein Negm
Hussein Negm

Reputation: 601

at the first glance I would say that using intersect is a good solution so this query should do the trick for you 

SELECT userID2
FROM friends 
WHERE userID = $fromID 
INTERSECT
SELECT userID2
FROM friends 
WHERE userID = $theOtherID

EDIT: another less clear solution would be

SELECT userID2
FROM friends INNER JOIN friends f2
USING (userID2)
WHERE friends.UserID = $fromID AND f2.userID = $theOtherID

Upvotes: -1

eggyal
eggyal

Reputation: 125865

SELECT t.f FROM friends JOIN (

  SELECT IF(userID = $id, userID2, userID) f
  FROM   friends
  WHERE  state AND $id IN (userID, userID2)

) t ON t.f IN (userID, userID2)
WHERE  state AND $session IN (userID, userID2)

See it on sqlfiddle.

Upvotes: 3

Related Questions