How to select random rows in Doctrine 2 (standalone or symfony 2 & 3) easily

Either for learn something new or simply because we need to select random items, records from a table in our database and we don't have time to implement a new Doctrine function (Rand in this case which is not implemented tipically in dql), this task can become tricky if we don't use the correct functions.

In mySql you can use the following code to select random rows from a table easily :

SELECT column FROM table
ORDER BY RAND()
LIMIT 10

But if you use DQL (Doctrine Query Language) this will be not so easy. According to the documentation you can implement a doctrine extension and add the RAND instruction to our queries (In this article, we will not handle this because that's another history and another headache) but we will solve this issue easily and quickly only with a couple of lines instead add entire classes to our project.

For our solution, we will use the where IN statement and we will simply search by some term (in this case ID or any autoincremental field, if your table doesn't have a primary key with autoincremental, you must implement the RAND extension from doctrine ) according to random numbers with a simple php function.

In this example, the table that we will use will have an autoincremental field called id (which have numerics values ...), to retrieve random records we need to create first a function that returns random numbers with a given range (the start value,the max value and the quantity) like the following :

function UniqueRandomNumbersWithinRange($min, $max, $quantity) {
        $numbers = range($min, $max);
        shuffle($numbers);
        return array_slice($numbers, 0, $quantity);
}

UniqueRandomNumbersWithinRange will gives us random numbers between the given range, those numbers will be used to search random rows in our table with doctrine like this:

$em = $this->getDoctrine()->getManager();
$repo = $em->getRepository('mybundleBundle:TableName');
$quantity = 5; // We only want 5 rows (however think in increase this value if you have previously removed rows on the table)

// This is the number of rows in the database
// You can use another method according to your needs
$totalRowsTable = $repo->createQueryBuilder('a')->select('count(a.id)')->getQuery()->getSingleScalarResult();// This will be in this case 10 because i have 10 records on this table

$random_ids = UniqueRandomNumbersWithinRange(1,$totalRowsTable,$quantity);
// var_dump($random_ids);
// outputs for example:
// array(1,5,2,8,3);

$random_articles = $repo->createQueryBuilder('a')
            ->where('a.id IN (:ids)') // if is another field, change it
            ->setParameter('ids', $random_ids)
            ->setMaxResults(3)// Add this line if you want to give a limit to the records (if all the ids exists then you would like to give a limit)
            ->getQuery()
            ->getResult();

The previous code structure is pretty easy, the WHERE IN will match the rows which id's are in the $random_ids (array). You can change the method to get the total of rows in your database (or get the id of the last register according to the use of your table) if you need to. The most important part is the where in line, you can test with random numbers to see if it works before implement the complete solution. Have fun !