将包含案例语句的MySql查询转换为Codeigniter活动记录查询

问题描述

我有以下查询，希望使Codeigniter Active Record更加友好，但是我不确定如何向查询的设置部分添加case语句.

I have the following query which I want to make more Codeigniter Active Record friendly but I'm not sure how to go about adding case statements to the set part of the query.

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

我已经尝试过了，但是联接似乎并没有...联接.

I've tried this, but the joins don't seem to be... joining.

$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');

有什么想法吗?

推荐答案

尝试一下

$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
');

这篇关于将包含案例语句的MySql查询转换为Codeigniter活动记录查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！