编写MYSQL以下用户查询

Question

我建立一个应用程序，其中用户可以跟随其他用户，并遵循。

用户也可以看看谁另一用户以下。

现在让我们说USER1在看着谁user2的跟随，我需要找到所有用户2是继人的ID并将其与谁user1的下面。

而不是只返回所有符合该用户的ID都user1和user2（这是我在其他论坛上见过），我需要找回以下ID和用户名都user2的还有一个标志，指示跟随的人是否也跟着user1。

我知道了在PHP中使用双每个查询的循环工作，但我担心这样的代码将是昂贵的，并会好得多有一个MySQL查询优化。

相关表和列：

following_table 
    follower_id 
    followed_id 
    following: varchar -- 'true' or 'false' 

user_table 
    user_id 
    user_name 

这是我的PHP代码：

$user_id1 = '1991'; 
$myFollowingQuery = "SELECT following_table.followed_id, user_table.user_name 
        FROM following_table 
        INNER JOIN user_table ON 
        following_table.followed_id = user_table.user_id 
        WHERE following_table.following = 'true' 
        AND following_table.follower_id = '$user_id1'"; 

$user_id2 = '1985'; 
$userFollowingQuery = "SELECT following_table.followed_id, user_table.user_name 
         FROM following_table 
         INNER JOIN user_table ON 
          following_table.followed_id = user_table.user_id 
         WHERE following_table.following = 'true' 
         AND following_table.follower_id = '$user_id2'"; 

$userFollowingResult = mysql_query($userFollowingQuery) 
          or doResponse('error',"Couldn't connect to the database"); 
$myFollowingResult = mysql_query($myFollowingQuery) 
          or doResponse('error',"Couldn't connect to the database"); 

      for($i = 0; $i< mysql_num_rows($userFollowingResult);$i++){ 

$loopArray = array(followed_id => mysql_result($userFollowingResult,$i,"followed_id"), 
        followed_name => mysql_result($userFollowingResult,$i,"user_name")); 

for($j = 0; $j< mysql_num_rows($myFollowingResult);$j++){ 
    if(mysql_result($userFollowingResult,$i,"followed_id") 
    ==mysql_result($myFollowingResult,$j,"followed_id")) { 
    $loopArray['is_following'] = 'true'; 
    break; 
    } 
    if($j==mysql_num_rows($myFollowingResult)-1){ 
    $loopArray['is_following'] = 'false'; 
    break; 
    } 
} 

$resultArray[$i] = $loopArray; 
} 

echo json_encode($resultArray); 

Answer 1

下面是一个简单的查询：

http://sqlfiddle.com/#!2/6b8d6/3

SELECT 
    user.user_id, 
    user.user_name, 
    he.follower_id AS follower_id, 
    IF(me.followed_id,1,0) AS metoo 

FROM  following AS he 

INNER JOIN user 
    ON user.user_id = he.followed_id 

LEFT JOIN following AS me 
    ON me.follower_id = 1 
    AND me.followed_id = he.followed_id 

WHERE he.follower_id = 2