CODEWITHSUNDEEP
symfonydoctrine-ormdql
user3342506
user3342506

Reputation: 159

doctrine2: how to use the random custom function?

I use doctrine2 in the symfony2 framework. And I want to select the single random field. I don't want to use the native query or to get the random with PHP.

I tried to make this according to

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/dql-user-defined-functions.html

https://gist.github.com/Ocramius/919465

The extended function connected in the config.yml

orm:
    ...
    entity_managers:
        default:
            ...

            dql:
                numeric_functions:
                    rand: MyProject\MyBundle\DQL\Rand

I try to call the query in the controller

    $product= $em->createQueryBuilder('p')
                 ->select('w')
                 ->from('MyBundle:Product', 'p')
                // ->orderBy('RAND ()') // I tried so
                 ->addSelect('RAND() as HIDDEN rand') // and so
                 ->orderBy('rand')
                 ->getQuery()
                 ->getSingleResult();

and I tried also 

       $product = $em->createQuery('SELECT p FROM MyBundle:Product p ORDER BY RAND()')
                     ->setMaxResults(1)
                     ->getSingleResult();

I get the error:

           Error: Expected end of string, got '('

Note that, "ASC" follows after "RAND()" in the query. I tried to call orderBy('RAND()', ''), but unsuccesfully...

Upvotes: 1

Views: 893

Answers (1)

bob12
bob12

Reputation: 47

With this bundle : https://github.com/mapado/MysqlDoctrineFunctions

You can use the functions in your DQL Query :

$query = 'SELECT RAND(), ROUND(123.45) 
    FROM ...
';

$em->createQuery($query);

But you can't use ->orderBy...

4.8.2. Can I sort by a function (for example ORDER BY RAND()) in DQL?

No, it is not supported to sort by function in DQL. If you need this functionality you should either use a native-query or come up with another solution. As a side note: Sorting with ORDER BY RAND() is painfully slow starting with 1000 rows.

http://docs.doctrine-project.org/en/2.1/reference/faq.html#can-i-sort-by-a-function-for-example-order-by-rand-in-dql

Upvotes: 1

Related Questions