Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash· GoogleHugging Face LMArena Human Preference 140K

But it is giving me the following result: SELECT cl.customerid, cl.customertypeid,…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-3-7-sonnet-20250219-thinking-32k’s answer.
  1. 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.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 = ( AND cl3.customer_type_id = cl.customer_type_id GROUP BY cl.customer_id, cl.customer_type_id

    What could be the issue?

  2. Gemini 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 the ON clause.

    When you pass the $join string, 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 the GROUP BY.

    The Problematic Parts in Your Generated SQL:

    1. Missing SELECT MAX... and WHERE 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
      
    2. GROUP BY Inside LEFT JOIN:
      AND `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.
      
      The GROUP BY should 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's TRUE. Setting it to FALSE tells CodeIgniter to treat your $on string literally, without trying to parse or escape it.

    Also, remember the c2 vs cl2 typo 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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.