mysql - 在 LEFT JOIN 中使用别名

Question

我有一个表，用于存储友谊关系。

它有 id、userA 和 userB 列(userA 的 id 始终小于 userB 的 id)。

我尝试选择特定用户的所有好友关系:

SELECT CASE friendships.userA WHEN (?) THEN friendships.userB ELSE friendships.userA END AS friend_id, users.username FROM friendships
LEFT JOIN users ON friend_id=users.id WHERE userA = (?) OR userB = (?) ORDER BY timestamp DESC LIMIT ? OFFSET ?;

$stmt->bind_param("iiiii",  $user_id, user_id, $user_id, $requestsPerLoad_db, $offset);

我收到以下错误消息:

Unknown column 'friend_id' in 'on clause'

这个说法有什么问题吗？我可以获取 $row['friend_id'] 的值，但无法在左连接中使用它？

Answer 1

这是您的查询:

SELECT (CASE f.userA WHEN (?) THEN f.userB ELSE f.userA END) AS friend_id,
       u.username
FROM friendships f LEFT JOIN
     users u
     ON friend_id = u.id
WHERE f.userA = (?) OR f.userB = (?)
ORDER BY timestamp DESC
LIMIT ? OFFSET ?;

friend_id 是在 SELECT 中定义的，因此不能在同一查询的其他地方使用。您必须使用子查询。一种简单的解决方案是将逻辑移至 ON 子句。这是一种方法:

ON (u.id = f.userA and f.userB = ?) or
   (u.id = f.userB and f.userA = ?)

另一种方法是完全跳过LEFT JOIN并将逻辑移至WHERE子句:

SELECT u.id, u.username
FROM users u
WHERE EXISTS (SELECT 1 
              FROM friendships f
              WHERE f.userB = ? AND f.userA = u.id
             ) OR
      EXISTS (SELECT 1
              FROM friendships f
              WHERE f.userA = ? AND f.userB = u.id
             ) 
ORDER BY timestamp DESC
LIMIT ? OFFSET ?;

这种方法的优点是查询可以利用适当的索引。