CODEWITHSUNDEEP
phpdoctrine-ormdql
Yasser1984
Yasser1984

Reputation: 2451

How to select randomly with doctrine

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?

Upvotes: 44

Views: 74793

Answers (15)

Paul ALBERT
Paul ALBERT

Reputation: 281

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.

Upvotes: 0

hellcat thành
hellcat thành

Reputation: 1

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

Upvotes: 0

HMagdy
HMagdy

Reputation: 3295

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

Upvotes: 47

R Sun
R Sun

Reputation: 1671

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

Upvotes: -1

Andrés Moreno
Andrés Moreno

Reputation: 667

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.

Upvotes: 2

Jonny
Jonny

Reputation: 2333

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

Upvotes: 53

Kyeno
Kyeno

Reputation: 599

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

Upvotes: -2

PayteR
PayteR

Reputation: 1757

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

Upvotes: 0

Hossam
Hossam

Reputation: 1146

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

Upvotes: -2

Jazi
Jazi

Reputation: 6732

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

Upvotes: 7

BenMorel
BenMorel

Reputation: 36524

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

Upvotes: 49

Safwan Bakais
Safwan Bakais

Reputation: 129

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.

Upvotes: -1

KoeH
KoeH

Reputation: 1

Just add the following:

->orderBy('RAND()')

Upvotes: -8

araldh
araldh

Reputation: 51

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

Upvotes: 0

Derek
Derek

Reputation: 149

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.

Upvotes: 10

Related Questions