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

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 3

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 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

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 9

@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 10

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 11

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

Answer 12

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 13

Just add the following:

->orderBy('RAND()')