PHP中带JOIN的SQL查询返回重复行问题及解决诉求

问题概述 #

需要实现多维度用户搜索，核心需求如下：

• 匹配用户姓名、邮箱、地址等多个字段，同一用户匹配多个条件时不重复返回
• 支持通过关联的餐厅名称搜索用户
• 保留无餐厅关联的用户
• 现有问题：搜索时同一用户因匹配多个条件重复返回，使用SELECT DISTINCT仍无效

问题根源 #

1. JOIN类型错误：原SQL使用JOIN（内连接），直接过滤掉了没有餐厅关联的用户
2. 条件逻辑优先级问题：OR与AND混用未加括号，导致餐厅名称的匹配逻辑判断错误
3. 去重方式无效：SELECT DISTINCT包含restaurants.*，即使用户唯一，若用户多字段匹配或餐厅字段存在差异，仍会返回重复行

解决方案 #

优化后的SQL语句 #

通过子查询先筛选出符合条件的唯一用户ID，再关联用户表和餐厅表，确保每个用户仅返回一次：

SELECT u.*, r.*
FROM (
    SELECT DISTINCT u_inner.user_id
    FROM users2 u_inner
    LEFT JOIN restaurants r_inner ON u_inner.user_id = r_inner.fk_user_id
    WHERE 
        u_inner.user_email LIKE :search 
        OR u_inner.user_name LIKE :search
        OR u_inner.user_last_name LIKE :search
        OR u_inner.user_address LIKE :search
        OR u_inner.user_zip LIKE :search
        OR u_inner.user_city LIKE :search
        OR r_inner.restaurant_name LIKE :search
) AS filtered_users
JOIN users2 u ON filtered_users.user_id = u.user_id
LEFT JOIN restaurants r ON u.user_id = r.fk_user_id

调整后的PHP代码 #

简化参数绑定（仅需绑定一次搜索值），同时修正JOIN和条件逻辑：

require_once __DIR__ . '/../_.php';

try {
    $json = file_get_contents('php://input');
    $data = json_decode($json);
    $search = $data->search;

    if (empty($search)) {
        echo json_encode(['info' => 'Search string is empty']);
        exit;
    }

    $db = _db();

    // 优化后的SQL查询
    $q = $db->prepare("
        SELECT u.*, r.*
        FROM (
            SELECT DISTINCT u_inner.user_id
            FROM users2 u_inner
            LEFT JOIN restaurants r_inner ON u_inner.user_id = r_inner.fk_user_id
            WHERE 
                u_inner.user_email LIKE :search 
                OR u_inner.user_name LIKE :search
                OR u_inner.user_last_name LIKE :search
                OR u_inner.user_address LIKE :search
                OR u_inner.user_zip LIKE :search
                OR u_inner.user_city LIKE :search
                OR r_inner.restaurant_name LIKE :search
        ) AS filtered_users
        JOIN users2 u ON filtered_users.user_id = u.user_id
        LEFT JOIN restaurants r ON u.user_id = r.fk_user_id
    ");

    // 仅需绑定一次搜索参数
    $q->bindValue(':search', "%{$search}%");

    $q->execute();
    $result = $q->fetchAll();

    echo json_encode($result);
} catch (Exception $e) {
    $status_code = !ctype_digit($e->getCode()) ? 500 : $e->getCode();
    $message = strlen($e->getMessage()) == 0 ? 'error - ' . $e->getLine() : $e->getMessage();
    http_response_code($status_code);
    echo json_encode(['info' => $message]);
}

关键优化点 #

• LEFT JOIN：保留无餐厅关联的用户，避免数据丢失
• 子查询去重：先通过DISTINCT锁定唯一用户ID，从根源避免多字段匹配导致的重复
• 简化参数绑定：统一使用一个:search参数，减少冗余代码
• 逻辑修正：确保餐厅名称匹配的条件正确关联到所属用户

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