Welcome Guest, Not a member yet? Register   Sign In
ActiveRecord order by random broken on MySQL ?
#1

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

[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;
#3

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

[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
#5

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

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

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

I changed my base model class to explode() the comma separated order parameter and call order_by for each segment.




Theme © iAndrew 2016 - Forum software by © MyBB