MySQL random rows sorted by a column name

Question

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.

Did you try using Zend_Db_Profiler to see what request was generated ? (see framework.zend.com/manual/en/zend.db.profiler.html ) — Pascal MARTIN
– Pascal MARTIN, Commented Jul 17, 2009 at 19:17
@Pascal: I got back to work today, and used __toString() on the SQL: SELECT projects.* FROM projects WHERE (cid = 1) ORDER BY RAND(), name ASC LIMIT 3 So yes, what I thought I wanted was being generated. — X-Istence
– X-Istence, Commented Jul 20, 2009 at 17:18

Adam Byrtek · Accepted Answer · 2009-07-17 20:08:48Z

4

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.

edited Jul 17, 2009 at 20:08

answered Jul 17, 2009 at 19:59

Adam Byrtek

12.2k2 gold badges35 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

X-Istence Over a year ago

When I get back to work on Monday I will test this solution. I have up-voted for letting me know why mine would not work!

X-Istence Over a year ago

This worked perfectly, using the Zend Framework Zend_Db_Select class the query ended up being: SELECT c.* FROM (SELECT projects.* FROM projects WHERE (cid = 1) ORDER BY RAND() LIMIT 3) AS c ORDER BY name ASC

Martin The Genius · Accepted Answer · 2011-12-21 15:37:43Z

1

$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!

answered Dec 21, 2011 at 15:37

Martin The Genius

111 bronze badge

Comments

gnarf · Accepted Answer · 2009-07-18 09:51:23Z

0

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

answered Jul 18, 2009 at 9:51

gnarf

107k26 gold badges129 silver badges161 bronze badges

Comments

Richard Knop · Accepted Answer · 2009-07-18 12:45:15Z

0

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.

answered Jul 18, 2009 at 12:45

Richard Knop

84.2k155 gold badges402 silver badges564 bronze badges

2 Comments

X-Istence Over a year ago

That is exactly what I did if you look at my posting, except I used Zend_DB_Expr and an array to pass it to order() which is how the documentation says it should be used if you are using a database expression. The SQL generated is exactly the same: SELECT projects.* FROM projects WHERE (cid = 1) ORDER BY RAND(), name ASC LIMIT 3

X-Istence Over a year ago

The issue is that it does not work, it does not ORDER BY RAND() and then limit to 3, and then sort by name.

Collectives™ on Stack Overflow

MySQL random rows sorted by a column name

Original Question:

Answer

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Original Question:

Answer

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related