Active record backticks problem

[eluser]Unknown[/eluser]
Hi All,

I have a function that looks like this:

Code:

function get_document_list($curproj=0)
    {
        $this->db->select('document.*,users.firstname,users.lastname');
        $this->db->from('document');
        $this->db->join('users', 'users.id = document.document_director','left');        
        if($curproj > 0)
        {
            $this->db->where('document_project', $curproj);
        }
        $this->db->order_by('SUBSTR(`document_number`, 1,1) ASC, CAST(SUBSTR(`document_number`, 2) AS SIGNED) ASC');

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

The problem is that the query ends up like this:

Code:

SELECT `document`.*, `users`.`firstname`, `users`.`lastname` FROM (`document`) LEFT JOIN `users` ON `users`.`id` = `document`.`document_director` WHERE `document_project` = '23' ORDER BY SUBSTR(`document_number`, `1`, `1)` ASC, CAST(SUBSTR(`document_number`, `2)` AS SIGNED) ASC

Basically, it's putting backticks around the parameters for the SUBSTR MySQL function call. What would be the best way to handle this - do I need to just build my query and put it into a db->select() call with FALSE as the second parameter or is there another way?

Gary

[eluser]cahva[/eluser]
Yes, add FALSE as a second parameter to the select call. order_by method does not have option to remove protect identifiers so you cant do anything about that without hacking that method.

But I dont think thats a problem as you can add those SUBSTR and CAST stuff as fields to select and give'em some field name and add those to order_by.

[eluser]nate_02631[/eluser]
Just curious - is this a bug? I was struggling with this for a little bit with my query and it seems to affect in some places but not in others (like when I do JOINS)... I saw in the manual it makes mention of the FALSE parameter to rid the backticking, but makes no mention that it is because it can cause conflict with field.names (by backticking at the dots)...

[eluser]LuckyFella73[/eluser]
I don't know if that's a bug. I had the same problem a few weeks ago
and solved that by setting up the select like that:

Code:

$this->db->select('document.*');
$this->db->select('users.firstname');
$this->db->select('users.lastname');

Hope that works for you.



EDIT: I didn't read your post taking the time I should have,
so my post is useless ... sorry