mysql - 如何组合这两个 MySQL 语句

Question

我正在使用这两个语句来查询 BuddyPress 的好友关系，但我想知道它们是否是更好的方法或组合这两个 MySQL 语句的方法:

    //Statement for friendship initiator
    $SQL1 = "SELECT wp_users.display_name AS'name', 
                    wp_users.user_login AS 'fname', 
                    wp_users.user_nicename AS 'surname' 
            FROM wp_users 
            INNER JOIN wp_bp_friends 
                  ON wp_users.id = wp_bp_friends.friend_user_id 
            WHERE 1=1 
               AND wp_bp_friends.initiator_user_id = " . $user_id . " 
               AND wp_bp_friends.is_confirmed = 1";


    //Statement for friendship non-initiator
    $SQL2 = "SELECT wp_users.display_name AS 'name', 
                    wp_users.user_login AS 'fname', 
                    wp_users.user_nicename AS 'surname' 
             FROM wp_users 
             INNER JOIN wp_bp_friends 
                   ON wp_users.id = wp_bp_friends.initiator_user_id 
             WHERE 1=1 
                   AND wp_bp_friends.friend_user_id = " . $user_id . " 
                   AND wp_bp_friends.is_confirmed = 1";

结果应包含基于 $user_id 的好友列表，无论他们是否发起了关系。

Answer 1

$sql = "
  SELECT u.display_name  AS name,
         u.user_login    AS fname,
         u.user_nicename AS surname
  FROM   wp_users        AS u
    JOIN wp_bp_friends   AS f
      ON (u.id, $user_id) IN (
           (f.friend_user_id,    f.initiator_user_id),
           (f.initiator_user_id, f.friend_user_id)
         )
  WHERE  f.is_confirmed = 1
";

<小时/>

编辑

每@Quassnoi's comment下面，与上面的答案一样优雅，它不会使用索引进行查找(由于 MySQL 实现缺陷)。相反，你可以这样做:

$sql = "
  SELECT u.display_name  AS name,
         u.user_login    AS fname,
         u.user_nicename AS surname
  FROM   wp_users        AS u
    JOIN wp_bp_friends   AS f
      ON (f.friend_user_id    = u.id AND f.initiator_user_id = $user_id)
      OR (f.initiator_user_id = u.id AND f.friend_user_id    = $user_id)
  WHERE  f.is_confirmed = 1
";