How to fetch random rows from result set ?

Sometimes you might want to fetch random rows from a table.

For example let's assume we have a table name 'campaigns'. The task giving in this example is to fetch a random campaign from the table.

With Doctrine thats achieved easily like this:

$q = new Doctrine_Query();
$res = $q->select('c.*, RANDOM() rand')->from('Campaigns c')->orderby('rand')->limit(1)->execute()->getFirst();

Background explanation:

Doctrine actually ask the DB to add random number to the select columns and then sort by it. The DB fetch that number using an internal db function that select random value between 0.0 and 1.0.

For example here is a typical MySQL output:

mysql> select *, RAND() as rand from campaigns order by rand;
-------------------------
id, name, rand
-------------------------
1, xxx, 0.271449184298002
3, yyy, 0.410212622406159
2, zzz, 0.444912028913304
-------------------------

Please note:

The usage of random is quite expensive on huge selects.