How to get random element with doctrine/postgresql [symfony]-CodePudding

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?

CodePudding user response：

<?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()
        ;
    }
}