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?

Chemulpo

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

Reduplicate

Ok I tried the first method, It seems like the rand function is being added to doctrine in bootstrap, I still get this error "Error: 'rand' is not defined." I'm using a DQL that looks like this $dql = "SELECT w FROM DbEntities\Entity\Word w WHERE w.indictionary = 0 AND w.frequency > 3 order by rand()"; assuming that the function is accepted by doctrine, how should I be using it? – Chemulpo

Did you register the function with addCustomNumericFunction() in your Doctrine configuration, as mentioned on the page? Also, try to use RAND in uppercase, not sure whether it is case sensitive or not. – Reduplicate

Yes, and I did use upper case, didn't help. I went with your second suggestion, native queries, I don't know if I'm gonna be facing more limitations in future using this or not, hopefully not. Thank you very much. – Chemulpo

Ok, according to this link, you can only ORDER BY a custom function by SELECTing it first. That should read something like SELECT w, RAND() AS r FROM Word w ORDER BY r. – Reduplicate

"Issue a raw SQL query first to get some IDs randomly", how do you do that ? Native query ? Isn't it the same problem ? – Massachusetts

No, by raw query, I mean directly on the connection: $em->getConnection()->query('SELECT id FROM table ORDER BY RAND()'); Then fetch the ids, and pass this array of ids as a parameter to a DQL query. – Reduplicate

@Wickramaranga Thanks for pointing this out; the Doctrine projected has transitioned from Jira to the GitHub issue tracker, and the doc link was outdated. I updated the links. – Reduplicate

@Benjamin Thank you :D – Zizith

For anyone looking at point 3 from the answer - this will not work for MySQL. There is no order guarantees (or lack of thereof) in SQL dialects that can be imposed by IN(). It would not matter what order IDs you'd put there. Most probably you'll get results ordered by ID descending if you do not provide your own ORDER BY clause. – Skinflint

@Skinflint Sure, the order of the IN() is not preserved, but the numbers you pass to IN() have already been randomized. Depending on whether the set of random numbers being potentially ordered differently by the DB is a problem for you, you might want to reorder them according to the original order in software. – Reduplicate

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()')

Nolin

this is absolutely the right answer! For any problem, you will find always an extension for ^^ – Selfreliance

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

Bibliolatry

This is clearly the best solution in my opinion, because you can still use DQL/querybuilder and Doctrine, but also have the SQL performance. For me, the orderBy clause needed to be 'rand' instead of 'rand()' to work though (which makes sense, becuause you are using a var instead of calling a function). – Ferine

This answer points up a way of dealing with providing random results in a query in a way that perhaps back in 2014 it was the right solution. However, as explained by @Nolin there is a simpler way. There's no need to define the extra class Rand. – Hogtie

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.

Boxing

What if I have limit. I'll always get the same n and then shuffle them. Nos to good solution. – Discombobulate

This is quick and handy if you just want to pick a random for data fixtures. – Doridoria

"Very inefficient" is a pretty important detail. – Press

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);
    }
}

Ninos

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.

Barnard

This isn't exactly random, this approach gives total of 6 different combinations. – Ricci

@nacholibre you're right.This way it will never be same to RAND(). If someone wants improve combinations, they must add more columns. If someone wants have behaviour RAND(), better read other answers. Greetings – Tuberculosis

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

Covenant

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://mcmap.net/q/65469/-mysql-select-10-random-rows-from-600k-rows-fast.

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);
    }
}

Bismuthinite

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();
}

Plunk

symfony4.4 , i can use this – Raines

Please fix code formatting – Tondatone

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.

Marcoux

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.

Horatius

Answer 12

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

Collenecollet

That won't work if there were any unassigned IDs, so it needs at least two DB calls (in case PHP chooses a used ID in the randomizer) and in worst case it uses much more queries – Press

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();
}

Underwater

What if an id in between is missing maybe because the entity was deleted? Wouldn't it come to an error? For example $id_limits returns "1" as min and "1000" as max ... randomizing between 1 and 1000 gives you 430 ... but entity 430 was deleted before ... – Selfdeprecating

@JimPanse then it will retrieve entity 431 (if available) as the query is filtering using a larger than operand: entity.id >= :random_id – Minton

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()
    ;
}

Cryan

That won't work if there were any unassigned IDs, so it needs at least two DB calls (in case PHP chooses a used ID in the randomizer) and in worst case it uses much more queries – Press

Answer 15

Just add the following:

->orderBy('RAND()')

Lewd

Please add some explanation to your answer. By reading the other answers, I would guess that something more is needed? – Press