php - 将包含 case 语句的 MySql 查询转换为 codeigniter 事件记录查询

Question

我有以下查询，我想让 Codeigniter Active Record 更友好，但我不确定如何将 case 语句添加到查询的 set 部分。

UPDATE attendance_time_index ati
JOIN users s ON ati.time_index_user_id = s.user_id
JOIN attendance_code_groups acg ON s.user_attendance_code_group = acg.group_id
SET ati.time_index_default = CASE WHEN ati.time_index_value = 'PM' THEN acg.group_pm_default ELSE acg.group_am_default END
WHERE s.user_id = 123

我试过这个，但连接似乎不是......连接。

$this->db->join('users s','ati.time_index_user_id = s.user_id');
$this->db->join('attendance_code_groups acg', 's.user_attendance_code_group = acg.group_id');
$this->db->set('ati.time_index_default', 'CASE WHEN ati.time_index_value = 'PM' THEN acg.group_pm_default ELSE acg.group_am_default END', FALSE);
$this->db->where('s.user_id', 123);
$this->db->update('attendance_time_index ati');

有什么想法吗？

Answer 1

试试这个

$this->db->set('ati.time_index_default', "CASE WHEN ati.time_index_value = 'PM' THEN acg.group_pm_default ELSE acg.group_am_default END", FALSE);
$this->db->where('s.user_id', 123);
$this->db->update('attendance_time_index ati
join users s on ati.time_index_user_id = s.user_id
join attendance_code_groups acg  on s.user_attendance_code_group = acg.group_id
');