Original Question:
I am currently using Zend Framework with Zend_Db_*, and I am selecting three random rows from a table:
$category->getTable()->select()->order(new Zend_Db_Expr('RAND()'))->limit('3')
Where $category is a Zend_Db_Table_Row. I would like to grab three random rows, but have those three rows ordered by the column named name.
Changing the ->order() to the following had no effect:
->order(array(new Zend_Db_Expr('RAND()'), 'name ASC'))
As the entries are still showing up un-ordered, and still random.
Zend Framework solutions appreciated, but I can adapt other solutions to fit within my project.
I am aware of the scaling issues with using RAND(), the database will never get big enough for this to become an issue, the day it does I won't have to worry about maintaining it, the robots will, as I'll be long dead! :-P
Answer
For those wondering how this was ultimately completed using Zend_Db_Select, this is what it came down to use a sub-select within the Zend_Db_Select (I was using $category->findDefault_Model_projects() to find the dependent rowset, but that does not allow me to use the select() as a subselect until ZF-6461 fixes the issue, I am stuck with what I have):
$projects = new Default_Model_Projects();
$subselect = $projects->select()->order(new Zend_Db_Expr('RAND()'))->limit('3')->where('cid = ?', $category->id, Zend_Db::INT_TYPE);
$db = $projects->getAdapter();
$select = $db->select()->from(array("c" => new Zend_Db_Expr("({$subselect})")))->order('name');
$stmt = $select->query();
$projects = $stmt->fetchAll();
The generated SQL is:
SELECT `c`.* FROM (SELECT `projects`.* FROM `projects` WHERE (cid = 1) ORDER BY RAND() LIMIT 3) AS `c` ORDER BY `name` ASC
From there $projects contains the standard row set which can be iterated over much like any other database queries, the only thing it does not do is stick it in a table specific row/rowset class, which could have its downsides.
