Using RAND() function from SQL with Doctrine2

Question

I've been stuck with this for about 10 hours

I need to use this query (an optimized version of ORDER BY RAND)

public function findAllRandom()
{
   return $this->getEntityManager()
    ->createQuery(
        'SELECT p FROM GabrielUploadBundle:Image p WHERE RAND() < 0.0001 ORDER BY RAND() LIMIT 20')
    ->getResult();
}

And of course since I'm using DQL I need to implement the RAND() function

<?php
namespace Gabriel\UploadBundle\DoctrineFunctions;
use Doctrine\ORM\Query\Lexer;

/**
 * RandFunction ::= "RAND" "(" ")"
 */
class Rand extends \Doctrine\ORM\Query\AST\Functions\FunctionNode
{ 
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'RAND()';
    }
}

//Config.yml

orm:
(...)
    dql:
       numeric_functions:
         Rand: Gabriel\UploadBundle\DoctrineFunctions\Rand

The problem is I keep getting this error:

[Syntax Error] line 0, col 77: Error: Expected end of string, got '('

The source of the RAND() source code:

https://gist.github.com/Ocramius/919465

How to add from the config:

http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html

demo about adding functions to DQL

http://punkave.com/window/2012/07/24/for-the-php-crowd-adding-custom-functions-to-doctrine-2-dql

EDIT: After further research I found out that order by function isn't supported on dql: source: http://docs.doctrine-project.org/en/2.1/reference/faq.html#can-i-sort-by-a-function-for-example-order-by-rand-in-dql

To work around this the HIDDEN value can be added

public function findAllRandom()
{
   return $this->getEntityManager()
    ->createQuery(
        'SELECT p,RAND() AS HIDDEN rand FROM GabrielUploadBundle:Image p ORDER BY rand')
    ->getResult();
}

but for some reason it doesn't work with the WHERE clause

public function findAllRandom()
{
   return $this->getEntityManager()
    ->createQuery(
        'SELECT p,RAND() AS HIDDEN rand FROM GabrielUploadBundle:Image p WHERE rand < 0.00001 ORDER BY rand')
    ->getResult();
}

A solution for the where clause would be appreciated, everyone knows that using the ORDER BY RAND() function can slow down the server (in our case even crash it)

Answer 1

You can use a native SQL query from the controller

$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$statement = $connection->prepare("SELECT * FROM Image WHERE RAND()<(SELECT ((10/COUNT(*))*10) FROM Image) ORDER BY RAND() LIMIT 10");

$statement->execute();
$images = $statement->fetchAll();

Benchmark results http://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/

Answer 2

Take a look at this implementation - https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/Rand.php