But it is giving me the following result: SELECT cl.customerid, cl.customertypeid,…
- User
But it is giving me the following result:
SELECT
cl.customer_id,cl.customer_type_id, COUNT(cl.id) AS total_calls, MAX(cl.created_at) AS created_at,cl2.detailsASlatest_detailsFROMcall_logsASclLEFT JOINcall_logsAScl2ONcl.customer_id=cl2.customer_idANDcl.customer_type_id=cl2.customer_type_idANDcl2.created_at= ( ANDcl3.customer_type_id=cl.customer_type_idGROUP BYcl.customer_id,cl.customer_type_idWhat could be the issue?
Assistant
You've hit a classic CodeIgniter query builder quirk!
The issue is how CodeIgniter's
join()method handles complex strings, especially those containing newlines and full subqueries, when it tries to "auto-escape" or parse theONclause.When you pass the
$joinstring, CodeIgniter (by default) tries to smart-parse it to add backticks around table and column names. In doing so, with the newlines and the full subquery, it's getting confused and truncating your subquery prematurely, and even incorrectly placing theGROUP BY.The Problematic Parts in Your Generated SQL:
- Missing
SELECT MAX...andWHERE cl3.customer_id...:AND `cl2`.`created_at` = ( AND `cl3`.`customer_type_id` = `cl`.`customer_type_id` -- Missing the subquery's SELECT and first WHERE clause GROUP BYInsideLEFT JOIN:
TheAND `cl3`.`customer_type_id` = `cl`.`customer_type_id` GROUP BY `cl`.`customer_id`, `cl`.`customer_type_id` -- This GROUP BY is placed inside the JOIN ON clause, which is wrong.GROUP BYshould be at the very end of the main query.
The Solution: Disable Auto-Escaping for the Join Condition
The
join()method has an optional third parameter:$escape. By default, it'sTRUE. Setting it toFALSEtells CodeIgniter to treat your$onstring literally, without trying to parse or escape it.Also, remember the
c2vscl2typo from my previous answer.Corrected PHP Code:
$max_created_at = " SELECT MAX(created_at) FROM call_logs cl3 WHERE cl3.customer_id = cl.customer_id AND cl3.customer_type_id = cl.customer_type_id "; // Fix the c2 typo to cl2, and disable escaping for the join condition $join = "cl.customer_id = cl2.customer_id AND cl.customer_type_id = cl2.customer_type_id AND cl2.created_at = ($max_created_at)"; $this->db->select([ 'cl.customer_id', 'cl.customer_type_id', 'COUNT(cl.id) AS total_calls', 'MAX(cl.created_at) AS created_at', 'cl2.details AS latest_details' // Fixed c2 to cl2 ]) ->from("$this->tbl AS cl") ->join("$this->tbl AS cl2", $join, 'LEFT', FALSE) // Add FALSE here to disable escaping ->group_by('cl.customer_id, cl.customer_type_id'); // ... rest of your code $result = $this->db->get_compiled_select(); return $result;The Resulting Compiled SQL (Corrected):
With this change, the generated SQL will be:
SELECT `cl`.`customer_id`, `cl`.`customer_type_id`, COUNT(cl.id) AS total_calls, MAX(cl.created_at) AS created_at, `cl2`.`details` AS `latest_details` FROM `call_logs` AS `cl` LEFT JOIN `call_logs` AS `cl2` ON cl.customer_id = cl2.customer_id AND cl.customer_type_id = cl2.customer_type_id AND cl2.created_at = ( SELECT MAX(created_at) FROM call_logs cl3 WHERE cl3.customer_id = cl.customer_id AND cl3.customer_type_id = cl.customer_type_id ) GROUP BY `cl`.`customer_id`, `cl`.`customer_type_id`This corrected SQL query is syntactically valid and will achieve the desired result of getting the total calls, latest call timestamp, and details from the latest call for each customer/type group.
- Missing
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.