如何合并两个MySQL查询，生成含isLiked字段的JSON结果？

我掌握基础SQL查询，目前能从users表查询指定距离内的用户，查询会自动生成distance字段。现在想结合likes表（原本误以为是isLike表）的查询，给每个用户结果新增isLiked字段（值为true/false），判断登录用户是否点赞了该用户。

我了解UNION和JOIN的概念但未完全掌握，当前需要对每个用户单独发起点赞查询，效率极低，希望了解最优的合并方法，或确认思路是否有误，只需指明方向即可。

原代码示例 #

获取指定距离内用户的代码 #

$logged_user = $_GET["logged_user"];

$sql = "SELECT 
*,
(
   3959 *
   acos(cos(radians(52.41357)) * 
   cos(radians(lat)) * 
   cos(radians(lng) - 
   radians(-1.51314816)) + 
   sin(radians(52.41357)) * 
   sin(radians(lat )))
) AS distance 
FROM users 
HAVING distance < 10 
ORDER BY distance LIMIT 0,99";
$result = mysqli_query($conn, $sql) or die("Error in Selecting " . mysqli_error($conn));
 
$userOnlineStatus = array();
while($row = mysqli_fetch_assoc($result)){
    $userOnlineStatus[] = $row;
}

echo json_encode($userOnlineStatus);

输出示例：

[{"id":"10102","userID":"933ce029-e0b3-4e18-ad52-9e6c2019189b","name":"test","username":"test","verified":"YES","lat":"52.4194975","lng":"-1.5101260","status":"offline","distance":"0.4289222996651918"}]

单独查询点赞状态的代码 #

$logged_user = $_GET["logged_user"];
$to_user = $_GET["to_user"];

$sql="SELECT * FROM likes  WHERE to_user = '$to_user' AND logged_user = '$logged_user'";
$result = mysqli_query($conn, $sql) or die("Error in Selecting " . mysqli_error($conn));

if ($result->num_rows > 0) {
echo "true";
} else {
echo "false";
}

理想输出 #

[{"id":"10102","userID":"933ce029-e0b3-4e18-ad52-9e6c2019189b","name":"b","username":"933","verified":"YES","lat":"52.4194975","lng":"-1.5101260","status":"offline","distance":"0.4289222996651918","isLiked":"true"}]

解决方案方向 #

使用LEFT JOIN关联users表和likes表，配合条件判断生成isLiked字段，这是最高效的方式，避免多次查询：

1. 在原用户查询的基础上，通过LEFT JOIN关联likes表，关联条件为users.userID = likes.to_user，同时添加likes.logged_user = 登录用户ID的过滤条件
2. 用IF函数或CASE语句判断是否存在关联的点赞记录，生成isLiked字段（比如IF(likes.to_user IS NOT NULL, 'true', 'false') AS isLiked）
3. 若允许重复点赞，需用DISTINCT或GROUP BY users.userID去重，避免同一用户出现多条结果

修改后的SQL示例（建议用预处理语句防注入） #

SELECT 
    users.*,
    (
       3959 *
       acos(cos(radians(52.41357)) * 
       cos(radians(users.lat)) * 
       cos(radians(users.lng) - 
       radians(-1.51314816)) + 
       sin(radians(52.41357)) * 
       sin(radians(users.lat)))
    ) AS distance,
    IF(likes.to_user IS NOT NULL, 'true', 'false') AS isLiked
FROM users
LEFT JOIN likes ON users.userID = likes.to_user AND likes.logged_user = ?
HAVING distance < 10 
ORDER BY distance LIMIT 0,99

注意：用预处理语句绑定logged_user参数，不要直接拼接字符串，防止SQL注入。

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