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?

Sulphathiazole

Answer 1

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.

Fedak

Answer 2

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

Infusive

I like it, but I think I like gnarf's idea a little bit more. Just extending the Table Abstract for a random select works great. – Sulphathiazole

Yes, of course. My solution is more complex and comes from the thing that you want to create AN EXPRESSION based on an adapter. So it's generally reusable to any other project... But I agree that the table solution is more straightforward... – Lanell

At the bottom he also showed the Db_Expr that you can use for MSSQL and MySQL without having to modify it, letting it be generally reusable to any project as well. – Sulphathiazole

I thought the My_Db_Expr_Rand was the solution at first, but the problem is none of the underlying code ever passes the adapter currently in use to the Zend_Db_Expr while assembling the query. Thought about it for a minute and decided that the table was the closest tie in point with access to the adapter. – Fedak

Ah. My bad, i didn't check if the adapter is passed to expression. Sorry for that. – Lanell