将含JOIN子查询的原始SQL转换为CodeIgniter查询构造器

Great question! Let's break down how to translate your raw SQL with a subquery into CodeIgniter's query builder syntax. I'll cover both CodeIgniter 4 (the latest version) and CodeIgniter 3 since both are still widely used.

CodeIgniter 4 Implementation #

CI4 has built-in support for subqueries via the joinSub() method, which makes this task clean and straightforward:

public function fetchLatestInvoices()
{
    // Get the database connection instance
    $db = \Config\Database::connect();

    // Step 1: Build the subquery to grab the latest invoice ID per uniqueid
    $subquery = $db->table('invoices')
                   ->select('MAX(id) as maxid, uniqueid')
                   ->groupBy('uniqueid');

    // Step 2: Build the main query with joins
    $query = $db->table('invoices as inv2')
                ->select('inv2.id, inv1.uniqueid, inv2.pastamount_due, c.name')
                // Join the subquery as "inv1" using the specified ON condition
                ->joinSub($subquery, 'inv1', 'inv2.id = inv1.maxid AND inv2.uniqueid = inv1.uniqueid')
                // Join the client table to get customer names
                ->join('client as c', 'inv2.uniqueid = c.uniqueid')
                ->get();

    // Return results as objects (use getResultArray() for associative arrays if preferred)
    return $query->getResult();
}

Key Notes for CI4: #

• joinSub() takes three arguments: the subquery builder instance, the alias for the subquery, and the ON clause condition.
• This approach keeps your code maintainable and automatically handles SQL escaping, which is a big plus for security.

CodeIgniter 3 Implementation #

CI3 doesn't have a dedicated joinSub() method, but we can still achieve the same result by building the subquery as a compiled SQL string:

public function fetchLatestInvoices()
{
    // Step 1: Build the subquery and get its compiled SQL string
    $this->db->select('MAX(id) as maxid, uniqueid');
    $this->db->from('invoices');
    $this->db->group_by('uniqueid');
    $subquerySql = $this->db->get_compiled_select();

    // Reset the query builder to avoid leftover clauses affecting the main query
    $this->db->reset_query();

    // Step 2: Build the main query
    $this->db->select('inv2.id, inv1.uniqueid, inv2.pastamount_due, c.name');
    $this->db->from('invoices as inv2');
    // Join the subquery (wrapped in parentheses) with the alias "inv1"
    $this->db->join('(' . $subquerySql . ') as inv1', 'inv2.id = inv1.maxid AND inv2.uniqueid = inv1.uniqueid', 'inner');
    $this->db->join('client as c', 'inv2.uniqueid = c.uniqueid', 'inner');

    $query = $this->db->get();
    return $query->result();
}

Key Notes for CI3: #

• get_compiled_select() returns the SQL string of the subquery without executing it, which we can then insert into our main join.
• Always call reset_query() after generating the subquery to clear any existing clauses from the query builder, preventing unexpected behavior.
• The third argument in join() specifies the join type (we used 'inner' here to match your original query's implicit inner join).

Both implementations will generate exactly the same SQL as your original raw query, ensuring you get identical results while leveraging CodeIgniter's query builder benefits like cleaner code and automatic security handling.

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