How to Select Randomly with Doctrine

How to select randomly with doctrine

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

Get random records with Doctrine

I'm not aware of any way to ORDER BY RAND() "efficiently" from Doctrine. In your situation, the best thing is probably to get primary keys first, shuffle these keys and then make use of them within an IN statement.

You could also add a caching layer where to put (a subset of) the keys from the first query, especially if you have many records, so to avoid repeating the query on keys each time.

How to select randomly with Doctrine ODM?

I've solved my problem in this way:

public function getRandomUser()
{
    $qb = $this->getDocumentManager()->createQueryBuilder('AppBundle:User');
    $count =  $qb->getQuery()->count();
    $skip_count = random_int(0, $count);
    $qb->skip($skip_count);

    return $qb->getQuery()->getSingleResult();
}

Symfony doctrine DQL random result in Query with MaxResult

You need to create dql function for that. https://gist.github.com/Ocramius/919465 you can check that.

namespace Acme\Bundle\DQL;

use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;

class RandFunction extends FunctionNode
{
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'RAND()';
    }
}

After that open your config.yml file and add autoload that RandFunction.

orm:
        dql:
            numeric_functions:
                Rand: Acme\Bundle\DQL\RandFunction

And your query must be like:

$firstCategoryId = 50;

$repository = $this->entityManager->getRepository(BaseProduct::class);

        $products = $repository->createQueryBuilder('p')
            ->join('p.categories', 'c')
            ->addSelect('RAND() as HIDDEN rand')
            ->where('c.id = :categoryId')
            ->orderBy('rand')
            ->setParameter('categoryId', $firstCategoryId)
            ->getQuery()
            ->setMaxResults(4)
            ->getResult();

Symfony2 Doctrine get random product from a category

You first have to count the total number of products, then generate a random offset to select a random product.

This should get you started:

$count = $this->createQueryBuilder('u')
             ->select('COUNT(u)')
             ->getQuery()
             ->getSingleScalarResult();

And then you can generate a random number between your 1 and the total number of rows.

return $this->createQueryBuilder('u')
    ->where('u.category = :category')
    ->setFirstResult(rand(0, $count - 1))
    ->setMaxResults(1)
    ->setParameter('category', $category->getId())
    ->getQuery()
    ->getSingleResult()
;

Which translates to:

SELECT * FROM products WHERE category_id = ? LIMIT 1, {random offset}

How to fetch random row via Doctrine2 querybuilder?

The orderBy method should accept a field of Song for sorting purposes (such as 's.author' or 's.title'), and not a random value. Even if you chose a random field for ordering, such as selecting one randomly in php, this will not be very random at all, because you are always going to get the first result for the current sort criteria. If your songs have 8 fields, you would only get 8 different songs in your search results ever, even if you have thousands stored.

Here is a suggestion:

$qb1->select('s')
    ->from('\My\Entity\Song', 's')
    ->where('s.id <> ?1')
    ->setMaxResults(1)
    ->setParameters(array(1=>$current->id))
    ->setFirstResult($offset);

Here, $offset can be a random value you obtain in php via rand() or mt_rand() functions. Of course, $offset should be smaller than the total number of songs. This is just a suggestion, there are many ways you can accomplish this.

IMHO I think Doctrine2 is an extraordinary ORM, and there is nothing so advanced like it. I assume you read the Query Builder section of the reference guide, but I also suggest you read the DQL section, which explains what are the available functions within Doctrine query system, and how you can make your own (!).

Doctrine 2 Randomly Selecting a Row (Offset 0 or 1 indexed)?

setFirstResult() is 0-based.

Following your approach, you have to use:

->setFirstResult(rand(0, $count - 1))->setMaxResults(1)->getSingleResult();

---

Source:

I agree the documentation is unclear on that point. However, we can see that Doctrine\DBAL\Query\QueryBuilder uses it that way:

->modifyLimitQuery($query, $this->maxResults, $this->firstResult);

Which is then translated to SQL in Doctrine\DBAL\Platforms\AbstractPlatform:

final public function modifyLimitQuery($query, $limit, $offset = null)
{
    ...
    $query .= ' OFFSET ' . $offset;

OFFSET being 0-based in SQL, we can deduct that setFirstResult() is 0-based as well.

How to sort randomly with Doctrine DBAL, TYPO3 and TypoScript

The trick is to add RAND() to the fieldlist and assign an alias. The alias can be used for sorting.

lib.myElement = CONTENT
lib.myElement {
  wrap =  <div class="inner-wrapper">|</div>
  required = 1

  table = tt_content
  select.languageField = sys_language_uid
  select.pidInList = {$pidConstant}
  select.max = 1
  select.where = colPos = 0
  select.orderBy = my_alias
  select.selectFields = bodytext,image,header,header_link,RAND() as my_alias
  renderObj=COA
  renderObj{
      ...
  }
}

Get a random record from database/entity using CI2 + Doctrine2

This is what I've come up with:

$query = $this->doctrine->em->createQuery("select max(u.id) from ORM\Dynasties2\Femalenames u");
$result = $query->getSingleResult();
$highval = $result[1];
$random_name = rand(1,$highval);
$name = $this->doctrine->em->find('ORM\Dynasties2\Femalenames', $random_name);
$mother_name = $name->getName();

I assumed there was another way, but cannot discover it.

---

Related Topics