php - 结果中未考虑 NOT EXISTS

Question

我正在尝试从“comments”表中获取结果，但“readsbaby”表中的结果除外。我得到的结果来自注释，但没有 NOT EXISTS 语句的影响，因此结果是所有注释。

两个表都有不应包含在结果中的通用数据。我多次检查了数据和语法。不过，此查询将返回所有评论，而不考虑 AND NOT EXISTS 关闭。

public function get_user_comments($post_id)
{

$user_id = $this->session->userdata('id');
$group_id = $this->session->userdata('group_id');

  $sql = "SELECT * 
    FROM comments  
    WHERE DATE(created_on) > DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
    AND comments.group_id = " . $group_id . " 
    AND comments.user_id != " . $user_id . "  
        AND NOT EXISTS ( SELECT *
               FROM readsbaby
               WHERE comments.id = readsbaby.notification_id 
               AND comments.group_id = readsbaby.group_id  
               AND readsbaby.user_id = " . $this->session->userdata('id') . " 
               AND comments.nature1 = readsbaby.notification_type 
             )    ";

    return $data=$this->db->query($sql)->result_array();

}

期望获得通过NOT EXISTS关闭过滤的结果。

Answer 1

我会以这种方式更改查询，只包含表中没有任何匹配项的行readsbaby:

public function get_user_comments($post_id)
{

$user_id = $this->session->userdata('id');
$group_id = $this->session->userdata('group_id');

  $sql = "SELECT * 
    FROM comments 
    LEFT OUTER JOIN  readsbaby ON comments.id = readsbaby.notification_id 
               AND comments.group_id = readsbaby.group_id  
               AND readsbaby.user_id = " . $this->session->userdata('id') . " 
               AND comments.nature1 = readsbaby.notification_type

    WHERE DATE(created_on) > DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
    AND comments.group_id = " . $group_id . " 
    AND comments.user_id != " . $user_id . "  
    AND ISNULL(readsbaby.notification_id )";

    return $data=$this->db->query($sql)->result_array();

}