How to get random string through Doctrine2 querybuilder?

Question

So far I:

$qb1 = $this->getEntityManager()->createQueryBuilder(); $qb1->select('s') ->from('\My\Entity\Song', 's') ->where('s.id <> ?1') ->orderBy('RAND()', '') ->setMaxResults(1) ->setParameters(array(1=>$current->id)); 

But doctrine2 does not understand that:

 Error: Expected end of string, got '(' 

Even on the page of their requests there is nothing. You want to tell me that the best ORM for php does not have a random function?

Answer 1

The orderBy method should take the Songs field for sorting purposes (for example, 's.author' or 's.title'), and not a random value. Even if you chose a random field for ordering, for example, choosing one random case in php, it will not be very random at all, because you will always get the first result for the current sorting criteria. If your songs have 8 fields, you will only get 8 different songs in the search results, even if you have thousands of saved files.

Here is a suggestion:

 $qb1->select('s') ->from('\My\Entity\Song', 's') ->where('s.id <> ?1') ->setMaxResults(1) ->setParameters(array(1=>$current->id)) ->setFirstResult($offset); 

Here $ offset may be a random value that you get in php through the rand () or mt_rand () functions. Of course, $ offset should be less than the total number of songs. This is just a suggestion, there are many ways to do this.

IMHO I think Doctrine2 is an unusual ORM, and there is nothing so advanced. I assume that you have read the Query Builder section of the reference manual, but I also suggest you read the DQL , which explains what functions are available in the Doctrine query system, and how you can create your own (!).

Answer 2

You need to add a custom DQL RAND function. For the symfony2 framework, you can simply add to config:

 doctrine: orm: entity_managers: default: dql: numeric_functions: rand: DoctrineExtensions\Query\Mysql\Rand 

And add dependencies to you in composer.json:

composer require beberlei/DoctrineExtensions

Then the solution to get 100 random AcmeBundle:Item objects would be as simple as:

 $em = $this->getContainer()->get('doctrine')->getManager(); $messages = $em->createQueryBuilder() ->select('i, RAND() AS HIDDEN r') ->from('AcmeBundle:Item', 'i') ->orderBy('r', 'ASC') ->setMaxResults(100) ->getQuery() ->getResult(); 

Note: it is assumed that you are using a MySQL or MariaDB server. For SQLite or PostGreSQL, you may need the diffrent class.