Alright here's the situation, I have an application written in the Zend_Framework, that is compatible with both MySQL and MSSQL as the backend. Now, ZF is pretty good at solving a lot of the SQL discrepancies/differences between the two languages, but I still have yet to figure this one out.
The objective is to select 1 random record from the table, which is an extremely simple statement.
Here's a select statement for example:
$sql = $db->select()
->from("table")
->order("rand()")
->limit(1);
This works perfectly for the MySQL database tables, because the sql for MySQL is as follows:
SELECT `table`.* FROM `table` ORDER BY rand() ASC
Now MSSQL on the other hand, uses the newid() function to do randomizing.
Is there some sort of helper I can pass into the order() function in order to make it realize that it has to use the proper ordering? I searched the documentation and on the zfforums, found a few tips, but nothing solid.
One of the things I did find was:
ORDER BY RANDOM() not working - ZFForums.com
They are using the following:
$res = $db->fetchAll(
'SELECT * FROM table ORDER BY :random',
array('random' => new Zend_Db_Expr('RANDOM()')
);
It works... but I am not looking to build my select statement by typing it out and doing a replace on the string, I am trying to keep it in the same Zend_Db_Select object. I also have tried passing in the Zend_Db_Expr('RANDOM()')
into the ->order()
on the statement, and it fails. He also posts a theoretical solution to finding the answer, but I am not looking to rewrite the function this is within, modifying the $db->fetch() call.
Any ideas?
You could quickly abstract the function to a table - who knows which adapter it is using:
class MyTable extends Zend_Db_Table_Abstract {
public function randomSelect($select=null) {
if ($select === null) $select = $this->select();
if (!$select instanceOf Zend_Db_Select) $select = $this->select($select);
$adapter = $this->getAdapter();
if ($adapter instanceOf Zend_Db_Adapter_Mysqli) {
$select->order(new Zend_Db_Expr('RAND()'));
} else if ($adapter instanceOf Zend_Db_Adapter_Dblib) {
$select->order(new Zend_Db_Expr('NEWID()'));
} else {
throw new Exception('Unknown adapter in MyTable');
}
return $select;
}
}
$someSelect = $table->select();
// add it to an existing select
$table->randomSelect($someSelect);
// or create one from scratch
$select = $table->randomSelect();
Also, I found an article somewhere which I lost that recommended trying something like:
$select->order(new Zend_Db_Expr('0*`id`+RAND()));
to subvert MSSQL's query optimizer and trick it into calculating a new value for each row.