ORDER BY RANDOM() not working

BugTomek · #1 (**permalink**) 02-11-2008, 10:15 AM

Is there any way to use "ORDER BY RANDOM()" in Zend_Db? I tried it in Zend_Db_Select, I tried it in plain Zend_Db, I tried it with Zend_Db_Expr and it all ended with the same effect - no effect that is.
Or maybe someone knows any other way to bring the same functionallity, which is selecting a bunch of random rows from a database? Thanks.

BugTomek · #2 (**permalink**) 02-11-2008, 11:49 AM

OK, I found out how to do it - plain Zend_Db + Zend_Db_Expr is the answer.

PHP Code:


			
$res = $db->fetchAll(

    'SELECT * FROM table ORDER BY :random',

    array('random' => new Zend_Db_Expr('RANDOM()')

);

Voila!

This won't work:

PHP Code:


			
$select = $db->select();

$select->from('table')

          ->order(new Zend_Db_Expr('RANDOM()'));



$res = $db->fetchAll($select->__toString());

Actually $select->__toString() produces a proper sql query, but bad things happen on a $db->query() level, I suppose in a prepare/execute part as the produced error is something like "FUNCTION table.RANDOM does not exist".

I suppose making something like that

PHP Code:


			
$select = $db->select();

$select->from('table')

          ->order(new Zend_Db_Expr(':random'));



$res = $db->fetchAll(

     $select->__toString(),

    array('random' => new Zend_Db_Expr('RANDOM()')

);

could work too, but I'm not too sure if putting a placeholder into Zend_Db_Select using Zend_Db_Expr would really work + it doesn't actually look as a nice thing to make things work.

Zend_Db looks nice on a surface, but inside there's a lot of such small nitpicks you have to remember about.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode