开发好友请求系统：如何从关联表查询当前好友名称？

Hey there! Let's break down how to link your requests table with the member table to pull in your friend's names—this is a common JOIN scenario, so it's totally manageable once you see the pattern.

基础场景：获取当前用户作为接收方的好友名称 #

From your example, you're querying requests where user_id='2' (the recipient). To get the friend's name from the member table, you just need to JOIN the two tables using the friend_id (the requester) from requests and the user_id from member (assuming member uses user_id as its primary key and has a name column for the user's display name).

Here's the updated query:

SELECT 
  r.request_id,
  r.user_id AS recipient_id,
  r.friend_id AS requester_id,
  m.name AS friend_name
FROM requests r
INNER JOIN member m 
  ON r.friend_id = m.user_id
WHERE r.user_id = '2' 
  AND r.status = 1; -- 只筛选已通过的好友请求

进阶场景：获取当前用户所有双向好友 #

If you also want to include friends where user 2 was the requester (like request_id=6 in your sample data, where friend_id='2' and user_id='4'), you'll need to account for both sides of the relationship. You can do this with a UNION for clarity, or adjust the JOIN condition:

Option 1: Using UNION (clean and readable)

-- 接收的好友请求
SELECT 
  r.request_id,
  r.friend_id AS friend_id,
  m.name AS friend_name
FROM requests r
JOIN member m ON r.friend_id = m.user_id
WHERE r.user_id = '2' AND r.status = 1

UNION

-- 发起的好友请求
SELECT 
  r.request_id,
  r.user_id AS friend_id,
  m.name AS friend_name
FROM requests r
JOIN member m ON r.user_id = m.user_id
WHERE r.friend_id = '2' AND r.status = 1;

Option 2: Adjusted JOIN condition

SELECT 
  r.request_id,
  -- 动态判断好友ID：如果当前用户是接收方，好友是发起方；反之则是接收方
  CASE 
    WHEN r.user_id = '2' THEN r.friend_id 
    ELSE r.user_id 
  END AS friend_id,
  m.name AS friend_name
FROM requests r
JOIN member m 
  ON (r.user_id = '2' AND r.friend_id = m.user_id)
  OR (r.friend_id = '2' AND r.user_id = m.user_id)
WHERE r.status = 1;

Quick Notes: #

• Make sure the data types for user_id/friend_id in requests match the user_id in member (e.g., both strings or both integers) to avoid unexpected issues.
• The status=1 filter ensures you only pull in confirmed friend relationships—adjust this if you need to include pending requests too.

内容的提问来源于stack exchange，提问作者Kaleem Shahid