COALESCE未返回agent_id为0与NULL对应的Unassigned行问题求解

问题原因 #

你现在的查询返回不了Unassigned行，核心是两个问题：

• 你用的是默认的INNER JOIN，只会保留两张表关联匹配成功的记录，所有agent_id为0或NULL的房源记录在关联阶段就被直接过滤了，根本不会进入后续的COALESCE判断和分组逻辑，自然不会生成Unassigned分组
• SELECT语句里的COALESCE(c.display_name,'Unassigned')没有设置别名，同时你还额外查询了c.display_name，就算后续关联生效，视图里读取的还是原始的c.display_name（NULL值），也没法正常显示Unassigned

修复方案 #

1. 修改模型代码 #

把连接方式改为LEFT JOIN，调整SELECT字段的别名，对应修改分组规则：

function get_totalagentstatus(){
    $this->db->select("SUM(CASE WHEN t.status = 'D' THEN 1 END) AS draft,
    SUM(CASE WHEN t.status = 'N' THEN 1 END) AS unpublish,
    SUM(CASE WHEN t.status = 'Y' THEN 1 END) AS publish,
    SUM(CASE WHEN t.status = 'U' THEN 1 END) AS action,
    SUM(CASE WHEN t.status = 'L' THEN 1 END) AS unlisted,
    SUM(CASE WHEN t.status = 'S' THEN 1 END) AS sold,
    SUM(CASE WHEN t.status = 'T' THEN 1 END) AS let,
    COALESCE(c.display_name,'Unassigned') AS display_name,
    SUM(t.status = 'D') +SUM(t.status = 'N') + SUM(t.status = 'Y') + SUM(t.status = 'U') +
    SUM(t.status = 'L' ) + SUM(t.status = 'S' )+ SUM(t.status = 'T' ) AS total");
    $this->db->from('crm_listings t');
    // 改为左连接，保留所有房源记录
    $this->db->join('crm_clients_users c','t.agent_id = c.id','left');
    $this->db->where('archive="N"');
    // 按别名分组即可
    $this->db->group_by('display_name');
    $results = $this->db->get();
    return $results;
}

2. 可选适配逻辑 #

如果你的业务里agent_id=0也属于无对应代理的情况，且担心数据库里存在id=0的代理记录，可以在关联前先把0转为NULL，修改关联条件即可：

$this->db->join('crm_clients_users c','IF(t.agent_id=0,NULL,t.agent_id) = c.id','left');

改完之后所有agent_id为0或NULL的房源记录都会被分到Unassigned组下统计，控制器和视图的代码不需要做任何修改即可正常读取。

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