postgresqlsymfonyrandomdoctrine

How to get random element with doctrine/postgresql?


I have a symfony / doctrine project configured on postgresql and I would like to have a random result of an element of my table. How can I do that?


Solution

  • <?php
    
    // App/Doctrine/DBAL/FunctionNode/Random.php
    
    namespace App\Doctrine\DBAL\FunctionNode;
    
    use Doctrine\ORM\Query\AST\Functions\FunctionNode;
    use Doctrine\ORM\Query\Lexer;
    
    /**
     * RandFunction ::= "RANDOM" "(" ")".
     */
    final class Random extends FunctionNode
    {
        public function parse(\Doctrine\ORM\Query\Parser $parser): void
        {
            $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): string
        {
            return 'RANDOM()';
        }
    }
    
    # config/packages/doctrine.yaml
    doctrine:
        # ...
        orm:
            dql:
                numeric_functions:
                    Random: App\Doctrine\DBAL\FunctionNode\Random
    

    example of use

    <?php
    
    // App\Repository\EntityRepository.php
    
    namespace App\Repository;
    
    class EntityRepository extends ServiceEntityRepository
    {
        // ...
        public function getOneRandom()
        {
            return = $this->createQueryBuilder('alias')
                ->orderBy('RANDOM()')
                ->setMaxResults(1)
                ->getQuery()
                ->getOneOrNullResult()
            ;
        }
    }