CodeIgniter多表关联查询优化：拆分任务、用户、聊天数据

Hey there! Let's sort out this query problem and get you those three clean, separate arrays you're after. The issue with your current join setup is that it's mashing all three tables into one result set—this leads to duplicated data and makes it impossible to get the distinct arrays you need. Here are two straightforward solutions to fix this:

方法一：三次独立查询（推荐） #

This approach keeps your logic simple and avoids the mess of duplicated data from table joins. Since each of your target arrays comes from a single table, we can query them separately with the right filters:

获取任务详情数组 #

// 单条任务详情，用row_array()直接获取一维数组
$task_details = $this->db->select('task_number as taskno, user_id as owner, task_subject as subject, task_mention_link as link, task_module_key as key')
                         ->from('tasks_main')
                         ->where('task_number', 35)
                         ->get()
                         ->row_array();

获取用户ID列表数组 #

// 先查询符合条件的用户记录，再提取纯ID数组
$user_records = $this->db->select('user_id')
                         ->from('tasks_users_list')
                         ->where([
                             'task_number' => 35,
                             'task_users_type <' => 3
                         ])
                         ->get()
                         ->result_array();

// 提取出纯用户ID的一维数组
$user_id_list = array_column($user_records, 'user_id');

获取聊天消息列表数组 #

// 先查询聊天记录，再提取纯消息数组
$chat_records = $this->db->select('messages as msg')
                          ->from('tasks_chat')
                          ->where('task_number', 35)
                          ->get()
                          ->result_array();

// 提取出纯消息内容的一维数组
$chat_msg_list = array_column($chat_records, 'msg');

This method is easy to read, maintain, and performs well (especially if you have proper indexes on task_number across all tables).

方法二：一次查询后处理结果 #

If you prefer to run only one query, you can adjust your join logic and then clean up the result set to split into the three arrays:

调整后的关联查询 #

$this->db->select('tm.task_number as taskno, tm.user_id as owner, tm.task_subject as subject, tm.task_mention_link as link, tm.task_module_key as key, ul.user_id as users, tc.messages as msg');
$this->db->from("tasks_main as tm");
$this->db->join("tasks_users_list as ul", "tm.task_number = ul.task_number", "left");
$this->db->join("tasks_chat as tc", "tm.task_number = tc.task_number", "left"); // 改为左关联任务表，避免多余数据
$this->db->where([
    "tm.task_number" => 35,
    "ul.task_users_type <" => 3
]);
$query = $this->db->get();
$result = $query->result_array();

处理结果拆分数组 #

// 提取任务详情（取第一条的任务数据，因为关联查询会重复任务信息）
$task_details = [];
if (!empty($result)) {
    $task_details = [
        'taskno' => $result[0]['taskno'],
        'owner' => $result[0]['owner'],
        'subject' => $result[0]['subject'],
        'link' => $result[0]['link'],
        'key' => $result[0]['key']
    ];
}

// 提取去重的用户ID列表
$user_id_list = [];
foreach ($result as $row) {
    if (!empty($row['users']) && !in_array($row['users'], $user_id_list)) {
        $user_id_list[] = $row['users'];
    }
}

// 提取去重的聊天消息列表
$chat_msg_list = [];
foreach ($result as $row) {
    if (!empty($row['msg']) && !in_array($row['msg'], $chat_msg_list)) {
        $chat_msg_list[] = $row['msg'];
    }
}

Just note that this method requires extra work to remove duplicates, since the join will repeat task details for every user and chat message linked to the task.

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