Geek Answers Handbook

Zend_Db_Select order by random compatible in mssql / mysql

Ok, situation, I have an application written in Zend_Framework that is compatible with MySQL and MSSQL as a backend. Now ZF does a good job of resolving many SQL discrepancies / differences between the two languages, but I still have to figure it out.

The goal is to select 1 random entry from the table, which is an extremely simple statement.

Here is an example of a selection, for example:

$sql = $db->select()
      ->from("table")
      ->order("rand()")
      ->limit(1);

This works fine for MySQL database tables, because sql for MySQL looks like this:

SELECT `table`.* FROM `table` ORDER BY rand() ASC

Now MSSQL, on the other hand, uses the newid () function for randomization.

- , order(), , ? zfforums, , .

, , - :

ORDER BY RANDOM() - ZFForums.com

:

$res = $db->fetchAll(
'SELECT * FROM table ORDER BY :random',
array('random' => new Zend_Db_Expr('RANDOM()')
);

... select, , Zend_Db_Select. Zend_Db_Expr('RANDOM()') ->order() , . , , , $db- > fetch().

?

+5
2

- , :

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();

, -, , - :

$select->order(new Zend_Db_Expr('0*`id`+RAND()));

MSSQL .

+14

My_Db_Expr_Rand Zend_Db_Expr. , , .

+2

More articles:


All Articles