Zend_Db_Select order by random, compatible in mssql / mysql

Question

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?

Answer 1

I would create class My_Db_Expr_Rand extends Zend_Db_Expr. Bassed on the adapter I would return either one or the other.

Answer 2

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.