Active Record - Group by DATE_FORMAT()

[eluser]nevsie[/eluser]
Not 100% sure this is a bug, but it is something that does not work for me and i am unsure why and no one else could help - hence i am guessing it is a bug!

========================

i am trying to run this query through active record, but it seems to be causing errors when outputted, or at least causes problems in the final SQL string produced:
$this->db->group_by("DATE_FORMAT(expdate, '%Y-%m')");

seems to output in the sql string:
GROUP BY DATE_FORMAT(expdate, `'%Y-%m')`

am i doing something obviously wrong here - or ar the quotes being converted or…
note the full query runs when i manually enter it including it as:
GROUP BY DATE_FORMAT(expdate, '%Y-%m')

any help or ideas appreciated. or is it maybe a bug or missing feature from CI’s active record feature?

[eluser]danmontgomery[/eluser]
A quick look at the group_by function shows that it always protects identifiers.

Code:

/**
     * GROUP BY
     *
     * @access    public
     * @param    string
     * @return    object
     */
    function group_by($by)
    {
        if (is_string($by))
        {
            $by = explode(',', $by);
        }
    
        foreach ($by as $val)
        {
            $val = trim($val);
        
            if ($val != '')
            {
                $this->ar_groupby[] = $this->_protect_identifiers($val);
                
                if ($this->ar_caching === TRUE)
                {
                    $this->ar_cache_groupby[] = $this->_protect_identifiers($val);
                    $this->ar_cache_exists[] = 'groupby';
                }
            }
        }
        return $this;
    }

You can hack your way around it:

Code:

$this->db->_protect_identifiers = FALSE;
$this->db->group_by("DATE_FORMAT(expdate, '%Y-%m')");
$this->db->_protect_identifiers = TRUE;

[eluser]nevsie[/eluser]
ah okay, thanks for your response - understand why t does this, but a bit frustrating and messy turning it on and off... i will read up on the protect identifiers so i know a bit more, but thanks for the response.

[eluser]Karinsson[/eluser]
I know this is an old thread but i ran into the exact same problem, found this page on the first google search, and the fix above did not solve it.

It worked for me by simply parsing the group_by parameter as an array instead of a string as the comma in the DATE_FORMAT function will cause the string to be exploded.

Parsing a SQL function as a string returns an error (if the string has one or more commas)

Code:

$this->db->group_by("DATE_FORMAT(date, '%x-%v')";

// Generates GROUP BY DATE_FORMAT(date, `'%x-%v')`

Parsing a SQL function in an array does not

Code:

$this->db->group_by(array("DATE_FORMAT(date, '%x-%v')");

// Generates GROUP BY DATE_FORMAT(date, '%x-%v')