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?
The Doctrine team is not willing to implement this feature.
There are several solutions to your problem, each having its own drawbacks:
WHERE x.id IN(?) to load the associated objects, by passing the array of IDs as a parameter.ORDER BY RAND() exist, I won't detail them here, you'll find some good resources on this website).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.
Doctrine 2 do not support ORDER BY rand(), but I found this article where contains fixes for this is issue.
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');
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;
}
Just add the following:
->orderBy('RAND()')
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.
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);
}
}
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()')
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();
}
@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);
}
}
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()
;
}
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.
First get the MAX value from DB table & then use this as offset in PHP i.e $offset = mt_rand(1, $maxId)