Active Record order_by Problem [solved]

[eluser]Travis O[/eluser]
I have created a special ORDER BY condition that sorts my data alphabetically ignoring "The" if it is in the title.

When I use

Code:

$this->db->query('SELECT * FROM lb_courses WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(id,".",2),".",-1) = '.$category_id.' ORDER BY IF(SUBSTRING(course_name,1,4) = "The ", SUBSTRING(course_name,5), course_name)');

everything works fine, but when I try to do the same using Active Record syntax

Code:

$this->db->where('SUBSTRING_INDEX(SUBSTRING_INDEX(id,".",2),".",-1)',$category_id)->order_by('IF(SUBSTRING(course_name,1,4) = "The ", SUBSTRING(course_name,5), course_name)')->get('courses');

, I get the following error,

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' SUBSTRING(course_name, `5)`, `course_name)`' at line 4

SELECT * FROM (`lb_courses`) WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(id,".",2),".",-1) = '10' ORDER BY IF(SUBSTRING(course_name, `1`, `4)` = "The ", SUBSTRING(course_name, `5)`, `course_name)`

I'm fine with using "query", but I would prefer to use Active Record because it looks cleaner and it takes advantage of the prefix option in the database config.

Any insight would be helpful. Thank you.

[eluser]Travis O[/eluser]
I figured out a solution. The issue was caused by the back ticks inserted into the query, as seen in the error. I solved this by adding

Code:

$this->db->select('*',FALSE);

to remove the back ticks.

[eluser]jcavard[/eluser]
I was just made aware of this fantastic function

Code:

$this->db->_compile_select();

which renders the query, so you have a clue. This can come in handy