CodeIgniter 3.x中如何用Active Record编写SQL子查询语句

CI3的Active Record没有对窗口函数、派生表类的复杂查询做专门的快捷封装，通过「预编译子查询SQL + 外层查询引用派生表」的方式即可完全复现你给出的SQL逻辑，具体实现代码如下：

// 1. 预编译内层子查询，不实际执行，仅生成合法SQL字符串
// select第二个参数传false，关闭自动字段转义，避免窗口函数语法被错误转义
$this->db->select('c.name_prov, b.city, a.cases, ROW_NUMBER() OVER (PARTITION BY c.name_prov ORDER BY CAST(a.cases AS INT) DESC) AS rn', false);
$this->db->from('tb_cases a');
$this->db->join('tb_city b', 'a.id_city = b.id_city');
$this->db->join('tb_prov c', 'a.id_prov = c.id_prov');
$this->db->order_by('c.name_prov');
// 生成编译后的子查询SQL，调用后自动重置Active Record状态，不会和外层查询冲突
$subQuery = $this->db->get_compiled_select();

// 2. 构造外层查询，将预编译的子查询作为派生表使用
$this->db->select('a.name_prov AS prov, a.city, a.cases');
// 派生表必须设置别名，此处和原SQL保持一致使用别名a
$this->db->from('(' . $subQuery . ') a');
$this->db->order_by('a.name_prov', 'ASC');
$this->db->order_by('a.rn', 'ASC');

// 执行查询获取结果
$query = $this->db->get();
// 按需选择返回格式：result()返回对象数组，result_array()返回关联数组
$result = $query->result_array();

关键注意事项 #

• 编写包含窗口函数、类型转换等内置函数的SELECT字段时，必须给select()方法传入第二个参数false，关闭CI默认的自动字段转义逻辑，否则会出现窗口函数语法被加反引号、SQL执行报错的问题。
• 生成子查询优先使用get_compiled_select()方法，该方法不会实际执行查询，只会返回经过安全转义的合法SQL字符串，比手动拼接SQL的注入风险低很多。
• 子查询作为派生表传入from()方法时，必须给子查询设置和原SQL一致的别名，否则外层查询引用子查询内的字段时会报表不存在的错误。
• 多字段排序可以分多次调用order_by()方法，CI会按照调用顺序拼接排序规则，和原生SQL写多个排序字段的效果完全一致。

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