Symfony doctrine DQL random result in Query with MaxResult

Question

How can I get a random result with an dql Query?

This is my query:

$firstCategoryId = 50;

$repository = $this->entityManager->getRepository(BaseProduct::class);

        $products = $repository->createQueryBuilder('p')
            ->join('p.categories', 'c')
            ->where('c.id = :categoryId')
            ->setParameter('categoryId', $firstCategoryId)
            ->getQuery()
            ->setMaxResults(4)
            ->getResult();

This returns me always the first 4 products. Lets say the category with ID 50 has over 100 products. And what I want is querying randomly 4 articles from category with ID 50. But how? Is this possible? Of course I can set no Max Result and than do it with PHP... but this is not a good solution because of performance.

If you don't really care about speed, you can try to get all the products, shuffle the results array and then get the first 4 elements. — utnaf, Feb 17 '17 at 11:46
look [here](http://stackoverflow.com/questions/10762538/how-to-select-randomly-with-doctrine), this is already answered — iscato, Feb 17 '17 at 11:49
Possible duplicate of [How to select randomly with doctrine](http://stackoverflow.com/questions/10762538/how-to-select-randomly-with-doctrine) — Veve, Feb 17 '17 at 13:13

score 6 · Accepted Answer · answered Feb 17 '17 at 11:49

You need to create dql function for that. https://gist.github.com/Ocramius/919465 you can check that.

namespace Acme\Bundle\DQL;

use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;

class RandFunction extends FunctionNode
{
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'RAND()';
    }
}

After that open your config.yml file and add autoload that RandFunction.

orm:
        dql:
            numeric_functions:
                Rand: Acme\Bundle\DQL\RandFunction

And your query must be like:

$firstCategoryId = 50;

$repository = $this->entityManager->getRepository(BaseProduct::class);

        $products = $repository->createQueryBuilder('p')
            ->join('p.categories', 'c')
            ->addSelect('RAND() as HIDDEN rand')
            ->where('c.id = :categoryId')
            ->orderBy('rand')
            ->setParameter('categoryId', $firstCategoryId)
            ->getQuery()
            ->setMaxResults(4)
            ->getResult();

Symfony doctrine DQL random result in Query with MaxResult

1 Answers1