Orderby column RAND() error

[eluser]barbazul[/eluser]
A couple of days ago I found this bug marked as Resolved on the bug tracker.

I wasn't satisfied by the way it was resolved (by not mentioning the random ordering feature) so I looked for ways of really fixing the bug.

The reason why the guys at ellislab decided to make a short term solution was that each DB engine has a different way of calling the RAND() function

My idea of a good fix would be to set a key word to trigger random ordering like "random_order" or something like that.

I set out to look for the different ways of random ordering in the currently supported DB Drivers and the only big difference I found, was the way of calling the RAND() function, that is, the rest of the syntax remains untouched. This supported my idea for the fix so I prepared a list of changes to be made to DB_AR and to each driver.

I know for sure this will work in MySQL but I haven't tested in any of the other devices

File system/database/DB_active_record.php
Line 406

Code:

//Check for random ordering
if (strtolower($orderby) == ‘random’)
{
$orderby = $this->_random();
}

File system/database/drivers/mssql/mssql_driver.php
Line 478

Code:

// --------------------------------------------------------------------

/**
* Order criteria used for random ordering
*
* @return string
*/
function _random()
{
return “NEWID()”;
}

File system/database/drivers/mysql/mysql_driver.php
Line 485

Code:

// --------------------------------------------------------------------

/**
* Order criteria used for random ordering
*
* @return string
*/
function _random()
{
return “RAND()”;
}

File system/database/drivers/mysqli/mysqli_driver.php
Line 475

Code:

// --------------------------------------------------------------------

/**
* Order criteria used for random ordering
*
* @return string
*/
function _random()
{
return “RAND()”;
}

File system/database/drivers/oci8/oci8_driver.php
Line 601

Code:

// --------------------------------------------------------------------

/**
* Order criteria used for random ordering
*
* @return string
*/
function _random()
{
return “dbms_random.value”;
}

File system/database/drivers/odbc/odbc_driver.php
Line 449

Code:

// --------------------------------------------------------------------

/**
* Order criteria used for random ordering
*
* @return string
*/
function _random()
{
return “RND(”.time().")";
}

File system/database/drivers/postgre/postgre_driver.php
Line 482

Code:

// --------------------------------------------------------------------

/**
* Order criteria used for random ordering
*
* @return string
*/
function _random()
{
return “RANDOM()”;
}

File system/database/drivers/sqlite/sqlite_driver.php
Line 481

Code:

// --------------------------------------------------------------------

/**
* Order criteria used for random ordering
*
* @return string
*/
function _random()
{
return “RANDOM()”;
}

-- EDIT: removed an apparently offensive comment

[eluser]Derek Allard[/eluser]
"Sweep it under the carpet"? You say that like I'm trying to hide something. What on earth are you implying?

It was resolved as I removed the offending function. That said, as I said in my bug fix post, it wasn't the best long term solution, and I solicited your input in solving it.

I'd like to add this solution, but I can't until it can confirmed stable in all databases. Can you help with this testing?

[eluser]barbazul[/eluser]
Hi Derek,

I didn't mean to offend you at all and edited my post to clarify that.

That said, I hope someone finds this thread and posts some confirmation that the fix works so this can be added to the main core.

so PLEASE someone carry on with the testing!!!!!


I'll try to install some of them myself and test as soon as I have time to do so

[eluser]Derek Allard[/eluser]
Thanks for your input here barbazul.

I've implemented 'random' as an order by option. I believe it works, but I'd very much welcome your input. If you want to poke around, update system/database and all files with the files on the svn.

Code:

$this->db->order_by('field', 'random');

[eluser]barbazul[/eluser]
thanks derek!
i'll check it out as soon as i return from my vacations

[eluser]barbazul[/eluser]
I've downloaded the code and checked it through.
WOW! a LOT of updates in this past month only.
Congratulations for the great effort you are putting into pushing CI to it's ultimate ideal of being a code-as-fast-as-hell-with-almost-no-weight framework!!!

regarding the implementation of the random issue:
1) I went for a method instead of an attribute to make it more "template method pattern"-like but making it an attribute should work with no big differences.
2) What happened with the MSSQL and Oracle drivers? I suppose they didn't work or something... let me know and I'll research some more. (even better if someone who actually has access to such technologies do the research/testing)
3) The ODBC driver has a syntax error since you cannot put something like “RND(”.time().")" in the attribute definition. It should be set in the constructor.

[eluser]Derek Allard[/eluser]
Thanks sir.

Actually, that's just the half of it

Sincerely appreciate your feedback, the fixed odbc will roll into the svn soon, and I've also acocunted for mssql and oracle, but still don't have random working. They'll default to "asc". I'll document this. Have you seen the code needed to randomize those?

[eluser]barbazul[/eluser]
At the time I posted I had a lot of research done which I now have lost.
But the conclusion I reached at that time was that the correct way to randomize in them was as follows

MSSQL:
SELECT * FROM table ORDER BY NEWID()

Oracle:
dbms_random.value

Edit: A friend of mine tested on Oracle and it works fine. The query was:

Code:

SELECT * FROM table ORDER BY dbms_random.value