Code snippets for symfony 1.x

Navigation

Fetch a Random Record with Doctrine

To fetch a random user:

$userCount = Doctrine::getTable('User')->count();
$user = Doctrine::getTable('User')
  ->createQuery()
  ->limit(1)
  ->offset(rand(0, $userCount - 1))
  ->fetchOne();
 
by ericfreese on 2009-11-16, tagged doctrine  random  record 

Comments on this snippet

gravatar icon
#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?

gravatar icon
#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();
 
gravatar icon
#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... ;-|

gravatar icon
#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?

gravatar icon
#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);

gravatar icon
#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();

gravatar icon
#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...