How to select randomly with doctrine

Question

Here is how I query my database for some words

$query = $qb->select('w')
    ->from('DbEntities\Entity\Word', 'w')
    ->where('w.indictionary = 0 AND w.frequency > 3')
    ->orderBy('w.frequency', 'DESC')
    ->getQuery()
    ->setMaxResults(100);

I'm using mysql and I'd like to get random rows that match the criteria, I would use order by rand() in my query.

I found this similar question which basically suggests since ORDER BY RAND is not supported in doctrine, you can randomize the primary key instead. However, this can't be done in my case because I have a search criteria and a where clause so that not every primary key will satisfy that condition.

I also found a code snippet that suggests you use the OFFSET to randomize the rows like this:

$userCount = Doctrine::getTable('User')
     ->createQuery()
     ->select('count(*)')
     ->fetchOne(array(), Doctrine::HYDRATE_NONE); 
$user = Doctrine::getTable('User')
     ->createQuery()
     ->limit(1)
     ->offset(rand(0, $userCount[0] - 1))
     ->fetchOne();

I'm a little confused as to whether this will help me work around the lack of support for order by random in my case or not. I was not able to add offset after setMaxResult.

Any idea how this can be accomplished?

Answer 1

The Doctrine team is not willing to implement this feature.

There are several solutions to your problem, each having its own drawbacks:

• Add a custom numeric function: see this DQL RAND() function
  (might be slow if you have lots of matching rows)
• Use a native query
  (I personally try to avoid this solution, which I found hard to maintain)
• Issue a raw SQL query first to get some IDs randomly, then use the DQL WHERE x.id IN(?) to load the associated objects, by passing the array of IDs as a parameter.
  This solution involves two separate queries, but might give better performance than the first solution (other raw SQL techniques than ORDER BY RAND() exist, I won't detail them here, you'll find some good resources on this website).

Answer 2

In line with what Hassan Magdy Saad suggested, you can use the popular DoctrineExtensions library:

See mysql implementation here: https://github.com/beberlei/DoctrineExtensions/blob/master/src/Query/Mysql/Rand.php

# config.yml

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

Tested in Doctrine ORM 2.6.x-dev, you can then actually do:

->orderBy('RAND()')

Answer 3

Follow these steps:

Define a new class at your project as:

namespace My\Custom\Doctrine2\Function;

use Doctrine\ORM\Query\Lexer;

class Rand extends \Doctrine\ORM\Query\AST\Functions\FunctionNode
{

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $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)
    {
        return 'RAND()';
    }
}

Register the class config.yml:

doctrine:
     orm:
         dql:
             numeric_functions:
                 Rand: My\Custom\Doctrine2\Function\Rand

Use it directly as:

$qb->addSelect('RAND() as HIDDEN rand')->orderBy('rand()'); //Missing curly brackets

Answer 4

Or you could do this -->

$words = $em->getRepository('Entity\Word')->findAll();
shuffle($words);

Of course this would be very inefficient if you have many records so use with caution.

Answer 5

Why not to use repository?

<?php

namespace Project\ProductsBundle\Entity;

use Doctrine\ORM;

class ProductRepository extends ORM\EntityRepository
{
    /**
     * @param int $amount
     * @return Product[]
     */
    public function getRandomProducts($amount = 7)
    {
        return $this->getRandomProductsNativeQuery($amount)->getResult();
    }

    /**
     * @param int $amount
     * @return ORM\NativeQuery
     */
    public function getRandomProductsNativeQuery($amount = 7)
    {
        # set entity name
        $table = $this->getClassMetadata()
            ->getTableName();

        # create rsm object
        $rsm = new ORM\Query\ResultSetMapping();
        $rsm->addEntityResult($this->getEntityName(), 'p');
        $rsm->addFieldResult('p', 'id', 'id');

        # make query
        return $this->getEntityManager()->createNativeQuery("
            SELECT p.id FROM {$table} p ORDER BY RAND() LIMIT 0, {$amount}
        ", $rsm);
    }
}

Answer 6

For me, the most useful way was to create two arrays where i say order type and different properties of the Entity. For example:

    $order = array_rand(array(
        'DESC' => 'DESC',
        'ASC' => 'ASC'
    ));

    $column = array_rand(array(
        'w.id' => 'w.id',
        'w.date' => 'w.date',
        'w.name' => 'w.name'
    ));

You could add more entries to array $column like criteria.

Afterwards, you can build your query with Doctrine adding $column and $order inside ->orderBy. For example:

$query = $qb->select('w')
->from('DbEntities\Entity\Word', 'w')
->where('w.indictionary = 0 AND w.frequency > 3')
->orderBy($column, $order)
->getQuery()
->setMaxResults(100);

This way improved the performance of my application. I hope this helps someone.

Answer 7

Shuffling can be done on the query (array) result, but shuffling does not pick randomly.

In order to pick randomly from an entity I prefer to do this in PHP, which might slow the random picking, but it allows me to keep control of testing what I am doing and makes eventual debugging easier.

The example below puts all IDs from the entity into an array, which I can then use to "random-treat" in php.

public function getRandomArt($nbSlotsOnPage)
{
    $qbList=$this->createQueryBuilder('a');

    // get all the relevant id's from the entity
    $qbList ->select('a.id')
            ->where('a.publicate=true')
            ;       
    // $list is not a simple list of values, but an nested associative array
    $list=$qbList->getQuery()->getScalarResult();       

    // get rid of the nested array from ScalarResult
    $rawlist=array();
    foreach ($list as $keyword=>$value)
        {
            // entity id's have to figure as keyword as array_rand() will pick only keywords - not values
            $id=$value['id'];
            $rawlist[$id]=null;
        }

    $total=min($nbSlotsOnPage,count($rawlist));
    // pick only a few (i.e.$total)
    $keylist=array_rand($rawlist,$total);

    $qb=$this->createQueryBuilder('aw');
    foreach ($keylist as $keyword=>$value)
        {
            $qb ->setParameter('keyword'.$keyword,$value)
                ->orWhere('aw.id = :keyword'.$keyword)
            ;
        }

    $result=$qb->getQuery()->getResult();

    // if mixing the results is also required (could also be done by orderby rand();
    shuffle($result);

    return $result;
}

Answer 8

@Krzysztof's solution is IMHO best here, but RAND() is very slow on large queries, so i updated @Krysztof's solution to gives less "random" results, but they are still random enough. Inspired by this answer https://stackoverflow.com/a/4329492/839434.

namespace Project\ProductsBundle\Entity;

use Doctrine\ORM;

class ProductRepository extends ORM\EntityRepository
{
    /**
     * @param int $amount
     * @return Product[]
     */
    public function getRandomProducts($amount = 7)
    {
        return $this->getRandomProductsNativeQuery($amount)->getResult();
    }

    /**
     * @param int $amount
     * @return ORM\NativeQuery
     */
    public function getRandomProductsNativeQuery($amount = 7)
    {
        # set entity name
        $table = $this->getClassMetadata()
            ->getTableName();

        # create rsm object
        $rsm = new ORM\Query\ResultSetMapping();
        $rsm->addEntityResult($this->getEntityName(), 'p');
        $rsm->addFieldResult('p', 'id', 'id');

        # sql query
        $sql = "
            SELECT * FROM {$table}
            WHERE id >= FLOOR(1 + RAND()*(
                SELECT MAX(id) FROM {$table})
            ) 
            LIMIT ?
        ";

        # make query
        return $this->getEntityManager()
            ->createNativeQuery($sql, $rsm)
            ->setParameter(1, $amount);
    }
}

Answer 9

1. Create a new service in your Symfony project to register the custom DQL function: src/Doctrine/RandFunction.php

    namespace App\Doctrine;
    
    use Doctrine\ORM\Query\AST\Functions\FunctionNode;
    use Doctrine\ORM\Query\Lexer;
    
    class RandFunction extends FunctionNode
    {
        public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
        {
            return 'RAND()';
        }
    
        public function parse(\Doctrine\ORM\Query\Parser $parser)
        {
            $parser->match(Lexer::T_IDENTIFIER);
            $parser->match(Lexer::T_OPEN_PARENTHESIS);
            $parser->match(Lexer::T_CLOSE_PARENTHESIS);
        }
    }

(config/packages/doctrine.yaml):

    doctrine:
    orm:
        dql:
            string_functions:
                RAND: App\Doctrine\RandFunction

RAND() function, here post:

public function findRandomPosts()
{
    $query = $this->createQueryBuilder('p')
        ->orderBy('RAND()')
        ->setMaxResults(3)
        ->getQuery();

    return $query->getResult();
}

Answer 10

Warning :

Top answers forget something : What is randomly sorted with RAND() is the SQL query result rows. But if you make joins, it seems that the "root" objects (object from the main entity of the query) returned by doctrine are sorted following their 1st appearance in the SQL results rows.

That way, if we imagine a root object R1 linked to 1 child object, and a root object R2 linked to 10 children objects, if we sort the rows by RAND(), we'll have 10 times more probabilities that the R2 object appears before the R1 object in the return of a getResult() call.

A solution I've found is to generate a random value common to the whole query using $rand = rand();

Then to sort with : $qb->addOrderBy("rand(rootAlias.id + $rand)")

This way, each row of the same root object have the same random ordering value since they share the same seed.

Answer 11

I hope this would help others:

        $limit = $editForm->get('numberOfQuestions')->getData();
        $sql = "Select * from question order by RAND() limit $limit";

        $statement = $em->getConnection()->prepare($sql);
        $statement->execute();
        $questions = $statement->fetchAll();

Note here the table question is an AppBundle:Question Entity. Change the details accordingly. The number of questions is taken from the edit form, make sure to check the variable for the form builder and use accordingly.

Answer 12

First get the MAX value from DB table & then use this as offset in PHP i.e $offset = mt_rand(1, $maxId)

Answer 13

I know this is an old question. But I used the following solution to get the random row.

Using an EntityRepository method:

public function findOneRandom()
{
    $id_limits = $this->createQueryBuilder('entity')
        ->select('MIN(entity.id)', 'MAX(entity.id)')
        ->getQuery()
        ->getOneOrNullResult();
    $random_possible_id = rand($id_limits[1], $id_limits[2]);

    return $this->createQueryBuilder('entity')
        ->where('entity.id >= :random_id')
        ->setParameter('random_id', $random_possible_id)
        ->setMaxResults(1)
        ->getQuery()
        ->getOneOrNullResult();
}

Answer 14

Probably the easiest (but not necessarily the smartest) way to get a single object result ASAP would be implementing this in your Repository class:

public function findOneRandom()
{
    $className = $this->getClassMetadata()->getName();

    $counter = (int) $this->getEntityManager()->createQuery("SELECT COUNT(c) FROM {$className} c")->getSingleScalarResult();

    return $this->getEntityManager()

        ->createQuery("SELECT ent FROM {$className} ent ORDER BY ent.id ASC")
        ->setMaxResults(1)
        ->setFirstResult(mt_rand(0, $counter - 1))
        ->getSingleResult()
    ;
}

Answer 15

Just add the following:

->orderBy('RAND()')