Active record backticks problem

Active record backticks problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Active record backticks problem (/showthread.php?tid=33832)

Active record backticks problem - El Forum - 09-09-2010

[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

Active record backticks problem - El Forum - 09-09-2010

[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.

Active record backticks problem - El Forum - 12-13-2010

[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)...

Active record backticks problem - El Forum - 12-13-2010

[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