How to fetch random row via Doctrine2 querybuilder?

Question

So far I have:

$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 doesn't understand that:

Error: Expected end of string, got '('

Not even their querybuilder page has anything on it. Do you want to tell me that the best ORM for php doesn't have a random function?

Answer 1

The orderBy method should accept a field of Song for sorting purposes (such as 's.author' or 's.title'), and not a random value. Even if you chose a random field for ordering, such as selecting one randomly in php, this will not be very random at all, because you are always going to get the first result for the current sort criteria. If your songs have 8 fields, you would only get 8 different songs in your search results ever, even if you have thousands stored.

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 can be a random value you obtain in php via rand() or mt_rand() functions. Of course, $offset should be smaller than the total number of songs. This is just a suggestion, there are many ways you can accomplish this.

IMHO I think Doctrine2 is an extraordinary ORM, and there is nothing so advanced like it. I assume you read the Query Builder section of the reference guide, but I also suggest you read the DQL section, which explains what are the available functions within Doctrine query system, and how you can make your own (!).

Answer 2

You need to add custom DQL function RAND. For symfony2 framework you could simply add in config:

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

And add to you dependencies in composer.json:

composer require beberlei/DoctrineExtensions

Then, the solution for getting 100 random AcmeBundle:Item entities 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: this assumes you're useing MySQL or MariaDB backend. For SQLite or PostGreSQL you may need a diffrent implementation class.