Why isn't RAND() working for doctrine2

Question

We had this query on our DB (5 million records)

SELECT *
FROM foo
ORDER BY RAND()
LIMIT 20;

of course it was slow (2.7 seconds)

so we optimized the query to this (instead of ordering 5 million records it picks 100 and orders them):

SELECT *
FROM foo
WHERE RAND() < 0.00001
ORDER BY RAND()
LIMIT 20;

The query time is 0.002 seconds now

For a Symfony2 project we need to get this running on doctrine

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

But it throws this error

[Syntax Error] line 0, col 48: Error: Expected known function, got 'RAND'

I doesn't seem to know the RAND function

This is a query that would work (if it helps)

   public function findAllRandom()
    {
       return $this->getEntityManager()
        ->createQuery(
            'SELECT p FROM GabrielUploadBundle:Image p WHERE p.upvotes > 3')
        ->getResult();
    }

Answer 1

You have to explain the RAND() function to Doctrine. Note that the EntityManager's createQuery expects DQL, not SQL, and Doctrine does not know how to treat it, because probably it's not supported by all the Doctrine's DB backends.

Take a look at this answer, it has all the info on implementing RAND() in Doctrine manually.