+- 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 doesn't support ORDER BY FIELD() (/showthread.php?tid=25201)
Active record doesn't support ORDER BY FIELD() - El Forum - 12-03-2009
[eluser]LoRdCoStE[/eluser]
Hi,
as title say actually active record class does not support FIELD function in db->order_by.
The problem is that A.R. try to escape the function.
ex:
Code:
$this->db->order_by("FIELD(id, 129084,126792)");
produce:
ORDER BY FIELD(id, `129084`, `126792)`
but the correct query is:
ORDER BY FIELD(`id`, '129084', '126792')a temporary fix (not a good fix) is:
Code:
/* Location: ./system/database/DB_active_rec.php */
function order_by($orderby, $direction = '')
{
if (strpos($orderby, 'FIELD(') !== FALSE || strpos($orderby, 'FIELD (') !== FALSE)
{
$this->ar_orderby[] = $orderby;
return $this;
}
// ...
}Hope to see that implemented in the next version of CI.
Bye
Active record doesn't support ORDER BY FIELD() - El Forum - 12-04-2009
[eluser]CroNiX[/eluser]
Yeah, CI's active record doesn't work for everything, but its great for the most common things. When I encounter a problem with it like you did, I just write that particular query out from scratch and just use $db->query($query). I think thats better than hacking core files and its really such a rare occurrence unless you are doing pretty complex applications.
It would be nice if every method had the boolean option to protect identifiers or not like the select method does.
Active record doesn't support ORDER BY FIELD() - El Forum - 09-11-2013
[eluser]hyperfire[/eluser]
Just leaving this here to help others:
// set this to false so that _protect_identifiers skips escaping:
$this->db->_protect_identifiers = FALSE;
// your order_by line:
$this -> db -> order_by('FIELD ( products.country_id, 2, 0, 1 )');
// important to set this back to TRUE or ALL of your queries from now on will be non-escaped:
$this->db->_protect_identifiers = TRUE;