如何将指定NOT IN子查询SQL转换为CodeIgniter 4查询语句？

方法一：直接执行原生SQL #

对于这种多层嵌套的复杂子查询，直接用原生SQL执行是最直观省事的方式，代码如下：

// 定义原生SQL语句
$sql = "select agency from agency
where agency not in (
    SELECT agency FROM trip
    where trip_type = 'local trip'
    group by trip_type, agency
    having count(agency)=(select count(*) from role)
)";
// 执行查询
$query = $this->db->query($sql);
// 获取结果集，result_array()返回数组格式，result()返回对象格式
$result = $query->result_array();

方法二：使用CodeIgniter查询构造器 #

如果想遵循框架的查询构造器风格，可以分步嵌套构造子查询：

// 1. 构造最内层子查询：获取role表的总记录数
$subquery_role = $this->db->select('count(*)')->get_compiled_select('role');

// 2. 构造NOT IN中的子查询
$this->db->select('agency')
         ->from('trip')
         ->where('trip_type', 'local trip')
         ->group_by(['trip_type', 'agency'])
         ->having("count(agency) = ($subquery_role)");
$subquery_trip = $this->db->get_compiled_select();

// 3. 构造主查询，排除子查询中的agency
$this->db->select('agency')
         ->from('agency')
         ->where_not_in('agency', $subquery_trip);
$query = $this->db->get();
$result = $query->result_array();

说明：get_compiled_select()用于生成子查询的SQL字符串，方便嵌套到外层查询中。此方法适用于CodeIgniter 3版本，CI4的写法逻辑类似，仅部分语法细节有差异。

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