Zend Framework - applying order by on a nested query

Question

Hey guys

This might be a very simple thing. Check out the normal sql query below

(select * from shopping order by shopping_id desc limit 5) order by RAND()

This query runs successfully in mysql - not sure if this is the right way of doing it - but it works. It gets the last 5 ids from shopping table and randomly orders them everytime

I want to achieve this in Zend. I'm not sure how to execute the first part and then apply the RAND clause to the results - what I have below does not do that.

$select = $this->select()       
           ->from(array('sh'=>'shopping'))
           ->order('shopping_id desc')
           ->limit(5)    
           ->order('RAND()');

Answer 1

Why not take a slightly different approach which will acheive the same results. If you drop the subselect and the order by RAND() you can get the rows very quickly from the database, then when you are working with the rows, you could always randomize them.

$select = $this->select()       
       ->from(array('sh'=>'shopping'))
       ->order('shopping_id desc')
       ->limit(5)    

$rows = $this->fetchAll($select);

// take it from a rowset object, convert to an array:
$rowArray = array();
foreach ($rows as $row) $rowArray[] = $row;
shuffle($rowArray);

Answer 2

The Zend_Db_Expr class lets you do that. You create a new instance of the Zend_Db_Expr class and using its constructor you pass in the expression as a string: "RANDOM()".

$select = $this->select()
->from(array('sh'=>'shopping')) ->order('shopping_id desc') ->limit(5)
->order(new Zend_Db_Expr('RANDOM()'));