Get random records with Doctrine

Question

I wonder how to get a random number of Members from a Group, but I do not know what is the best way to do this, and I think ORDER BY RAND() is not the best alternative, as a Group can have more than 100,000 Members, performing this type of query could be very slow.

I found this way to make using SQL, but I do not know how to do the same thing in DQL: How can i optimize MySQL's ORDER BY RAND() function?

Answer 1

To not decrease performances I generally do as follows:

//Retrieve the EntityManager first
$em = $this->getEntityManager();

//Get the number of rows from your table
$rows = $em->createQuery('SELECT COUNT(u.id) FROM AcmeUserBundle:User u')->getSingleScalarResult();

$offset = max(0, rand(0, $rows - $amount - 1));

//Get the first $amount users starting from a random point
$query = $em->createQuery('
                SELECT DISTINCT u
                FROM AcmeUserBundle:User u')
->setMaxResults($amount)
->setFirstResult($offset);

$result = $query->getResult();  

Of course, the $amount users object you will retrieve are consecutive (i.e. the i-th, (i+1)-th,...,(i+$amount)-th), but usually there is the need of taking one or two entities at random, not the whole list. Hence, I think that this is an effective alternative.