$userCount = Doctrine::getTable('User')->count();
$user = Doctrine::getTable('User')
  ->createQuery()
  ->limit(1)
  ->offset(rand(0, $userCount - 1))
  ->fetchOne();
 

Comments on this snippet

#1 Frank Steiner on 2009-11-16 at 07:45

I do so hope you would be joking....

btw.: why do i have to register for writing comments? And why in Discordias holy name must my password be shorter then 10 characters?

#2 Michael Boyd on 2009-11-16 at 08:01

Wow... I have to agree with Frank: please be joking!

$user = Doctrine::getTable('User')
  ->createQuery()
  ->orderBy('RAND()')
  ->fetchOne();
 

#3 Frank Steiner on 2009-11-16 at 08:11

to tell you a little more:

if you count the resulting elements of a query (count(select * from foo)) the database has to execute the statement and to touch every resulting line.. then you take that count and use it for calculating an arbitary number where the DB has again to count all elements until the Nth one you wanted... ;-|

#4 ericfreese on 2009-11-17 at 03:31

My apologies for the inefficient solution -- I'm pretty new to Symfony and web development in general.

I was trying to implement the solution I found at http://snippets.symfony-project.org/snippet/25 for Doctrine instead of Propel. From what I understand, the orderBy('RAND()') solution isn't database independent. Is this correct?

If so, is there any other more efficient way to implement this in a database independent manner?

#5 Manuel Baldassarri on 2010-01-19 at 02:18

It isn't a bad solution as it seems but you have to replace the first line with that one

$userCount = Doctrine::getTable('User')->createQuery()->select('count(*)')->fetchOne(array(), Doctrine::HYDRATE_NONE);

#6 Manuel Baldassarri on 2010-01-19 at 02:24

Of course you have to replace $userCount with $userCount[0] in offset() :)

$userCount = Doctrine::getTable('User')->createQuery()->select('count(*)')->fetchOne(array(), Doctrine::HYDRATE_NONE); $user = Doctrine::getTable('User') ->createQuery() ->limit(1) ->offset(rand(0, $userCount[0] - 1)) ->fetchOne();

#7 Frank Steiner on 2010-01-26 at 09:51

yeah.. why use the easy and efficient way? counting the whole result-set is so much more fun then to get just the first one...