好友系统单查询实现：关联用户表展示好友及请求用户名

Got it, let's tackle this problem. You want a single SQL query to pull both your confirmed friends and pending friend requests, while correctly showing the other user's username—here's how to do it right:

-- Replace @current_user_id with the logged-in user's actual ID (e.g., $_SESSION['user_id'] in PHP)
SELECT
  -- Get the other user's ID, regardless of whether we sent or received the request
  CASE
    WHEN f.user_id = @current_user_id THEN f.friend_id
    ELSE f.user_id
  END AS other_user_id,
  -- Pull the other user's username by joining on the unified ID
  u.user_name AS other_user_name,
  -- Label the relationship status for easy display
  CASE
    WHEN f.confirmed = 1 THEN 'Confirmed Friend'
    WHEN f.user_id = @current_user_id THEN 'Pending Request Sent'
    ELSE 'Pending Request Received'
  END AS relationship_status,
  -- Optional: Keep original fields if you need to handle actions (like accepting requests)
  f.user_id AS request_sender_id,
  f.friend_id AS request_receiver_id,
  f.confirmed
FROM friends f
-- Join the users table to get the other user's name
LEFT JOIN users u 
  ON u.user_id = CASE
    WHEN f.user_id = @current_user_id THEN f.friend_id
    ELSE f.user_id
  END
-- Filter only relationships involving the current user
WHERE f.user_id = @current_user_id OR f.friend_id = @current_user_id
-- Optional: Sort by status and username for cleaner results
ORDER BY 
  relationship_status, 
  other_user_name;

How This Works: #

• Unified Other User ID: The first CASE expression normalizes the "other user" ID—whether you sent the request (so the other person is friend_id) or received it (so the other person is user_id). This lets us join the users table once to get the username, no messy multiple joins or subqueries.
• Clear Status Labels: The second CASE makes it easy to display what type of relationship each row is, so your frontend can show "Confirmed Friends" separately from pending sent/received requests.
• Efficient Filtering: The WHERE clause only pulls rows that involve the current user, so you don't get irrelevant data.

Pro Tips: #

• Add indexes on friends.user_id and friends.friend_id—this will speed up the query significantly as your user base grows.
• If you only want to show confirmed friends, add AND f.confirmed = 1 to the WHERE clause.
• If you need to exclude sent requests that haven't been accepted, adjust the WHERE or CASE logic as needed.

内容的提问来源于stack exchange，提问作者Matic Jan