CodeIgniter多用户等级登录场景下三表关联查询实现方法咨询

问题原因 #

你当前使用的是CodeIgniter查询构造器默认的INNER JOIN关联，只会返回同时在tbl_users和tbl_student中存在匹配关系的记录，讲师账号的扩展信息存储在第三张讲师表中，自然无法查询到结果。

解决方案1：单次左连接关联两张表（适合小数据量场景） #

如果你的tbl_users表中存在role字段区分用户角色，直接使用左连接同时关联学生表和讲师表即可，注意给同名字段设置别名避免覆盖：

$this->db->select('tbl_users.*, 
    tbl_student.student_name as student_name, 
    tbl_student.student_id as student_relation_id,
    tbl_lecturer.lecturer_name as lecturer_name,
    tbl_lecturer.lecturer_id as lecturer_relation_id');
$this->db->from('tbl_users');
// 左连接学生表，无匹配时学生相关字段返回null
$this->db->join('tbl_student', 'tbl_student.student_id = tbl_users.user_id', 'left');
// 左连接讲师表，无匹配时讲师相关字段返回null
$this->db->join('tbl_lecturer', 'tbl_lecturer.lecturer_id = tbl_users.user_id', 'left');
$this->db->where('tbl_users.idnumber', $idnumber);  
$this->db->where('tbl_users.password', $password);
$query = $this->db->get();
$user = $query->row_array();

// 后续根据角色字段判断使用对应扩展数据即可
if ($user) {
    if ($user['role'] == 'student') {
        // 学生登录逻辑
    } elseif ($user['role'] == 'lecturer') {
        // 讲师登录逻辑
    }
}

解决方案2：分两次查询（性能更优，适合大数据量场景） #

先查询用户基础信息校验账号密码，再根据返回的用户角色关联对应扩展表，避免不必要的关联操作：

// 第一步校验账号密码，查询用户基础信息
$this->db->select('*');
$this->db->from('tbl_users');
$this->db->where('idnumber', $idnumber);  
$this->db->where('password', $password);
$user = $this->db->get()->row_array();

if (!$user) {
    // 账号密码错误直接返回
    return false;
}

// 第二步根据角色关联对应扩展表
$role_info = [];
if ($user['role'] == 'student') {
    $this->db->select('*');
    $this->db->from('tbl_student');
    $this->db->where('student_id', $user['user_id']);
    $role_info = $this->db->get()->row_array();
} elseif ($user['role'] == 'lecturer') {
    $this->db->select('*');
    $this->db->from('tbl_lecturer');
    $this->db->where('lecturer_id', $user['user_id']);
    $role_info = $this->db->get()->row_array();
}
// 合并用户基础信息和角色扩展信息即可使用

注意事项 #

• 不要明文存储密码，建议使用PHP内置的password_hash()加密存储，登录时用password_verify()做校验
• 多表关联查询时，相同字段名需要手动指定别名，否则后查询的字段值会覆盖先查询的同名字段
• CodeIgniter查询构造器join()方法第三个参数不传时默认是inner，需要按需调整为left、right等关联类型

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