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?
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.