How to select randomly with doctrine

Question

Here is how I query my database for some words

$query = $qb->select('w')     ->from('DbEntities\Entity\Word', 'w')     ->where('w.indictionary = 0 AND w.frequency > 3')     ->orderBy('w.frequency', 'DESC')     ->getQuery()     ->setMaxResults(100); 

I'm using mysql and I'd like to get random rows that match the criteria, I would use order by rand() in my query.

I found this similar question which basically suggests since ORDER BY RAND is not supported in doctrine, you can randomize the primary key instead. However, this can't be done in my case because I have a search criteria and a where clause so that not every primary key will satisfy that condition.

I also found a code snippet that suggests you use the OFFSET to randomize the rows like this:

$userCount = Doctrine::getTable('User')      ->createQuery()      ->select('count(*)')      ->fetchOne(array(), Doctrine::HYDRATE_NONE);  $user = Doctrine::getTable('User')      ->createQuery()      ->limit(1)      ->offset(rand(0, $userCount[0] - 1))      ->fetchOne(); 

I'm a little confused as to whether this will help me work around the lack of support for order by random in my case or not. I was not able to add offset after setMaxResult.

Any idea how this can be accomplished?

Answer 1

The Doctrine team is not willing to implement this feature.

There are several solutions to your problem, each having its own drawbacks:

• Add a custom numeric function: see this DQL RAND() function
  (might be slow if you have lots of matching rows)
• Use a native query
  (I personally try to avoid this solution, which I found hard to maintain)
• Issue a raw SQL query first to get some IDs randomly, then use the DQL WHERE x.id IN(?) to load the associated objects, by passing the array of IDs as a parameter.
  This solution involves two separate queries, but might give better performance than the first solution (other raw SQL techniques than ORDER BY RAND() exist, I won't detail them here, you'll find some good resources on this website).

Answer 2

Follow these steps:

Define a new class at your project as:

namespace My\Custom\Doctrine2\Function;  use Doctrine\ORM\Query\Lexer;  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()';     } } 

Register the class config.yml:

doctrine:      orm:          dql:              numeric_functions:                  Rand: My\Custom\Doctrine2\Function\Rand 

Use it directly as:

$qb->addSelect('RAND() as HIDDEN rand')->orderBy('rand()'); //Missing curly brackets