Codeigniter 3.1.7：用Query Builder拼接含GROUP_CONCAT的子查询

Got it, let's walk through converting your raw SQL query into CodeIgniter 3.1.7's Query Builder syntax step by step. This approach keeps your code maintainable and leverages CI's built-in security features where possible.

Step 1: Set up the base select for product fields #

First, start by selecting the core fields from the product table. CI's Query Builder will automatically handle backticks for you here, so you don't need to add them manually:

$this->db->select('product.id, product.slug, product.name, product.images, product.description, product.price');

Step 2: Add the GROUP_CONCAT subquery #

Your query includes a subquery to fetch concatenated model values from the vehicle table. Since CI 3 doesn't have a dedicated select_subquery method, we'll build the subquery separately, compile it to a string, then embed it into the main select:

// Build the subquery
$subquery = $this->db->select('GROUP_CONCAT(model)', FALSE)
                     ->from('vehicle')
                     // Match product_id with a LIKE pattern, including the ESCAPE clause from your original query
                     ->where('vehicle.product_id LIKE CONCAT("%", product.id, "%") ESCAPE "!"', NULL, FALSE)
                     ->get_compiled_select();

// Add the subquery as an alias in the main select
$this->db->select("($subquery) AS model", FALSE);

• The FALSE parameter in select() tells CI not to escape the SQL string (critical for subqueries and aggregate functions like GROUP_CONCAT).
• We use CONCAT() to safely insert the product.id into the LIKE pattern instead of hardcoding it, which avoids syntax issues.

Step 3: Add the JOIN clauses #

Next, replicate your INNER JOINs with CI's join() method. This matches exactly what your raw SQL does:

$this->db->join('product_category', 'product_category.id = product.product_category_id');
$this->db->join('product_category_detail', 'product_category_detail.id = product.product_category_detail_id');

Step 4: Add your WHERE conditions (incomplete in your original query) #

Since your original WHERE clause was cut off, here's an example of how to add conditions using Query Builder. For instance, if you wanted to filter by a category name:

// Example: Filter where product_category.name equals "Electronics"
$this->db->where('product_category.name', 'Electronics');

Adjust this to match your actual incomplete WHERE logic.

Step 5: Execute the query and get results #

Finally, run the query and retrieve the results. You can use result() for an array of objects, or result_array() for an associative array:

$query = $this->db->get('product');
$products = $query->result(); // Or $query->result_array();

Full Example Code #

Putting it all together in a model method (common practice in CI):

public function get_products_with_models() {
    $this->db->select('product.id, product.slug, product.name, product.images, product.description, product.price');
    
    $subquery = $this->db->select('GROUP_CONCAT(model)', FALSE)
                         ->from('vehicle')
                         ->where('vehicle.product_id LIKE CONCAT("%", product.id, "%") ESCAPE "!"', NULL, FALSE)
                         ->get_compiled_select();
    
    $this->db->select("($subquery) AS model", FALSE);
    
    $this->db->join('product_category', 'product_category.id = product.product_category_id');
    $this->db->join('product_category_detail', 'product_category_detail.id = product.product_category_detail_id');
    
    // Add your WHERE conditions here
    // $this->db->where('...');
    
    $query = $this->db->get('product');
    return $query->result();
}

Key Notes #

• Always use FALSE in select() when working with subqueries or raw SQL fragments to prevent CI from incorrectly escaping your code.
• The get_compiled_select() method converts the subquery's Query Builder chain into a raw SQL string, which we then embed into the main query.
• If your LIKE pattern doesn't actually need the ESCAPE clause, you can remove it from the where() statement.

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