如何在CodeIgniter中使用FROM子句实现嵌套子查询？

原生可运行SQL #

$this->db->query("
    SELECT table_A.*
    FROM (
        SELECT MIN(table_A.table_A_id) AS table_A_id
        FROM (
            SELECT table_A.table_b_id
            FROM table_A
            JOIN table_b ON table_b.table_b_id = table_A.table_b_id
            WHERE table_b.table_c_id = 0 AND table_A.is_correct = 'y'
            GROUP BY table_A.table_b_id
        ) AS table_b_target
        JOIN table_A ON table_A.table_b_id = table_b_target.table_b_id
        GROUP BY table_A.table_b_id
    ) AS first_row
    JOIN table_A ON table_A.table_A_id = first_row.table_A_id
    WHERE table_A.is_correct = 'y'
")->result();

尝试的错误构造器代码 #

$this->db->select("table_A.*");
$this->db->from("","AS first_row");

$this->db->group_start();
$this->db->select_min('table_A.table_A_id');
$this->db->from("","AS table_b_target");

$this->db->group_start();
$this->db->select("table_A.table_b_id");
$this->db->from("table_A");
$this->db->join("table_b", "table_b.table_b_id = table_A.table_b_id");
$this->db->where("table_b.table_c_id =", 0);
$this->db->where("table_A.is_correct =", "y");
$this->db->group_by("table_A.table_b_id");
$this->db->group_end();

$this->db->join("table_A", "table_A.table_b_id = table_b_target.table_b_id");
$this->db->group_by("table_A.table_b_id");
$this->db->group_end();

$this->db->join("table_A", "table_A.table_A_id = first_row.table_A_id");
$this->db->where("table_A.is_correct =", "y");
$result = $this->db->get()->result();

正确的查询构造器实现 #

CodeIgniter查询构造器处理嵌套子查询时，需先构建内层子查询并转为SQL字符串，再将其作为临时表传入外层查询（group_start()仅用于构建条件分组，不能用来嵌套子查询）。以下是对应原生SQL的正确改写：

// 1. 构建最内层子查询：获取符合条件的table_b_id集合
$subquery1 = $this->db->select('table_A.table_b_id')
                      ->from('table_A')
                      ->join('table_b', 'table_b.table_b_id = table_A.table_b_id')
                      ->where('table_b.table_c_id', 0)
                      ->where('table_A.is_correct', 'y')
                      ->group_by('table_A.table_b_id')
                      ->get_compiled_select();

// 2. 构建中间层子查询：获取每个table_b_id对应的最小table_A_id
$subquery2 = $this->db->select_min('table_A.table_A_id', 'table_A_id')
                      ->from("($subquery1) AS table_b_target")
                      ->join('table_A', 'table_A.table_b_id = table_b_target.table_b_id')
                      ->group_by('table_A.table_b_id')
                      ->get_compiled_select();

// 3. 构建最外层查询：关联table_A获取完整数据
$result = $this->db->select('table_A.*')
                   ->from("($subquery2) AS first_row")
                   ->join('table_A', 'table_A.table_A_id = first_row.table_A_id')
                   ->where('table_A.is_correct', 'y')
                   ->get()
                   ->result();

关键说明 #

• get_compiled_select()方法返回当前查询的SQL字符串（不执行查询），刚好作为子查询的临时表使用。
• 内层子查询需用括号包裹后指定别名，与原生SQL写法一致。
• 不要用group_start()处理子查询，它的作用是生成(条件1 AND 条件2)这类条件分组，和子查询逻辑无关。

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