+- 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: ActiveRecord order by random broken on MySQL ? (/showthread.php?tid=13282)
ActiveRecord order by random broken on MySQL ? - El Forum - 11-17-2008
[eluser]girv[/eluser]
Running:
$this->db->order_by('','random');
gives me the following error with mysql and mysqli drivers:
Error Number: 1054
Unknown column ' RAND() LIMIT 1' in 'order clause'
SELECT `id`, `text`, `grpid` FROM (`gn_tagline`) WHERE `grpid` = 1 ORDER BY ` RAND() LIMIT 1
First question: is that the correct way to specify random ordering? From reading the user guide and the code, it would appear so.
The error comes from a spurious escape quote just before the RAND(). I've traced it down to the mysql_driver.php::_escape_identifiers() function which appears to misbehave if the input string is empty. It constructs an escaped string of just two quotes (``) but then the final preg_replace at line 448, which is intended to remove duplicate quotes, removes one of the quotes leaving just "`" to be returned.
_escape_identifiers is being called from _protect_identifiers in DB_driver.php. I'm not sure if the problem is in _escape_identifiers itself or if it shouldn't be called with an empty string and the problem is in the _protect_identifiers logic, or higher up again.
ActiveRecord order by random broken on MySQL ? - El Forum - 11-18-2008
[eluser]ctype[/eluser]
Look like here also many breaking changes, now don't work old code :
- $this->db->orderby('','RAND()');
- $this->db->where('table.field!=',$user_id); ! but work "where('field!=',$user_id);"
Any way, small (but probably not good) fix:
DB_active_rec.php
function order_by($orderby, $direction = '')
replace line 867
if (strtolower($direction) == 'random')
to
if (strtolower($direction) == 'random' || strtolower($direction) == 'rand()')
mysql_driver.php
function _escape_identifiers($item)
add at top
if ($item == '')
{
return $item;
}
also replace
$str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;
to
$str = $item;
ActiveRecord order by random broken on MySQL ? - El Forum - 11-18-2008
[eluser]girv[/eluser]
I think explicitly using "RAND()" was deprecated a while ago in favour of using the "random" keyword, for better database independence in your code. It's RAND() on MySQL, but not on some other databases.
ActiveRecord order by random broken on MySQL ? - El Forum - 11-18-2008
[eluser]ctype[/eluser]
[quote author="girv" date="1227047288"]I think explicitly using "RAND()" was deprecated a while ago in favour of using the "random" keyword, for better database independence in your code. It's RAND() on MySQL, but not on some other databases.
[/quote]
I agree with this, but I don't has enough time to replace this (and test) in huge codebase.
probably CI needed global config keys to make new versions backward compatible
ActiveRecord order by random broken on MySQL ? - El Forum - 11-18-2008
[eluser]girv[/eluser]
1.7.0 also no longer seems to support:
$this->db->order_by('f1,f2,f3');
You need to make separate calls to order_by for each field.
ActiveRecord order by random broken on MySQL ? - El Forum - 11-18-2008
[eluser]ImageSmith[/eluser]
girv,
try putting in some whitespace between the arguments. eg
Code:
$this->db->order_by('f1 , f2 , f3')This will get the field quotes behaving if I am not mistaken.
cheers,
dan
ActiveRecord order by random broken on MySQL ? - El Forum - 11-18-2008
[eluser]girv[/eluser]
@ImageSmith thanks
On re-reading the user guide, I think that syntax may really be intended just for the case where you have a sort direction after each field name. Adding the spaces seems to "fool" the processing into doing the right thing
I changed my base model class to explode() the comma separated order parameter and call order_by for each segment.