CODEWITHSUNDEEP
phpmysqlzend-frameworkzend-dbzend-db-table
X-Istence
X-Istence

Reputation: 16667

MySQL random rows sorted by a column name

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.

Upvotes: 2

Views: 3469

Answers (4)

Martin The Genius
Martin The Genius

Reputation: 11

$subQuery = $this->select()->from('picture')->order(new Zend_Db_Expr('RAND()'))->limit(count($this->selectAll()));
$select->setIntegrityCheck(false)
       ->from($subQuery);

This is what I did and it works. Cheers!

Upvotes: 1

Richard Knop
Richard Knop

Reputation: 83725

Try this:

$select = $this->select();
$select->order('RAND(), name');
$select->limit(3);
return $this->fetchAll($select);

That works for me here so it should work for you, too.

Upvotes: 0

gnarf
gnarf

Reputation: 106382

Why not just create a Rowset Subclass function that sorts the data by name?

Upvotes: 0

Adam Byrtek
Adam Byrtek

Reputation: 12202

Your initial solution is not correct because this query will generate a random value for each row and the order the rows based on it, sorting by name only if random values are equal (which is highly improbable).

The problem can be solved with a subquery like the one below

select * from (select * from categories order by rand() limit 3) c order by name

I'm leaving to you the task of translating this into Zend_Db language.

Upvotes: 4

Related Questions