CodeIgniter中利用XREF表实现管理员部门关联的SQL查询问题

没问题，我来帮你搞定这个需求！咱们分两部分来梳理：一是如何查询所有部门并标记管理员已归属的部门，二是如何将管理员添加到指定部门。

首先先明确下我假设的表结构（如果你的字段名不一样，对应调整就行）：

• Admin：admin_id（主键）、管理员名称等字段
• Departments：dept_id（主键）、dept_name（部门名称，比如General、2nd Line）
• Department_xref：admin_id、dept_id（联合主键，关联管理员和部门）

一、查询所有部门并标记管理员已选状态 #

要实现“显示所有部门，管理员所属部门复选框勾选”的效果，咱们需要用LEFT JOIN来关联部门表和中间关联表，确保所有部门都能被查询到，同时标记出当前管理员已关联的部门。

SQL 查询语句 #

SELECT 
    d.dept_id,
    d.dept_name,
    -- 用CASE判断当前管理员是否关联该部门，1表示已勾选，0表示未勾选
    CASE WHEN dx.admin_id IS NOT NULL THEN 1 ELSE 0 END AS is_selected
FROM Departments d
LEFT JOIN Department_xref dx 
    ON d.dept_id = dx.dept_id 
    -- 这里指定目标管理员ID，比如要查询第一位管理员就填1
    AND dx.admin_id = 1
ORDER BY d.dept_name;

CodeIgniter Query Builder 写法 #

如果用CI的查询构造器，代码可以这么写：

$targetAdminId = 1; // 要查询的管理员ID

$query = $this->db
    ->select('d.dept_id, d.dept_name, CASE WHEN dx.admin_id IS NOT NULL THEN 1 ELSE 0 END AS is_selected')
    ->from('Departments d')
    ->join('Department_xref dx', 'd.dept_id = dx.dept_id AND dx.admin_id = ' . $targetAdminId, 'left')
    ->order_by('d.dept_name')
    ->get();

// 获取结果集，后续可以传给前端渲染
$departments = $query->result();

前端渲染示例 #

拿到$departments后，前端循环渲染复选框时，根据is_selected值判断是否勾选：

<?php foreach ($departments as $dept): ?>
    <div class="form-check">
        <input class="form-check-input" type="checkbox" name="depts[]" value="<?= $dept->dept_id ?>" 
            <?= $dept->is_selected ? 'checked' : '' ?>>
        <label class="form-check-label"><?= $dept->dept_name ?></label>
    </div>
<?php endforeach; ?>

二、将管理员添加到指定部门（以2nd Line为例） #

要把第一位管理员添加到2nd Line部门，咱们需要先获取该部门的ID，然后插入到关联表中，同时要避免重复插入（比如管理员已经在这个部门的情况）。

SQL 实现方式 #

方式1：先查部门ID再插入（推荐，逻辑清晰）

-- 第一步：获取2nd Line部门的ID
SELECT dept_id FROM Departments WHERE dept_name = '2nd Line';

-- 第二步：插入关联记录（假设第一步查到的dept_id是2）
INSERT INTO Department_xref (admin_id, dept_id) VALUES (1, 2);

方式2：一次性插入（需确保admin_id+dept_id是联合主键）

如果Department_xref表的admin_id和dept_id设置了联合主键，可以用INSERT ... ON DUPLICATE KEY UPDATE来避免重复插入：

INSERT INTO Department_xref (admin_id, dept_id)
VALUES (1, (SELECT dept_id FROM Departments WHERE dept_name = '2nd Line'))
ON DUPLICATE KEY UPDATE admin_id = admin_id; -- 已存在则不做任何操作

CodeIgniter 代码实现 #

// 第一步：获取2nd Line部门的ID
$deptQuery = $this->db->select('dept_id')->where('dept_name', '2nd Line')->get('Departments');
$deptRow = $deptQuery->row();

if ($deptRow) {
    $deptId = $deptRow->dept_id;
    $adminId = 1; // 第一位管理员ID

    // 先检查是否已经关联过，避免重复插入
    $isAlreadyAssigned = $this->db
        ->where(['admin_id' => $adminId, 'dept_id' => $deptId])
        ->get('Department_xref')
        ->num_rows() > 0;

    if (!$isAlreadyAssigned) {
        // 插入关联记录
        $this->db->insert('Department_xref', [
            'admin_id' => $adminId,
            'dept_id' => $deptId
        ]);

        // 可以在这里添加插入成功后的提示或后续逻辑
        echo '管理员已成功添加到2nd Line部门！';
    } else {
        echo '管理员已经属于2nd Line部门了！';
    }
} else {
    echo '未找到2nd Line部门，请检查部门配置！';
}

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