how to get random entry from db?

Question

I need to get a random record from the database. How i do it:

$qb = $this->createQueryBuilder('article');
$totalRowsTable = $qb->select('count(article.id)')->getQuery()->getSingleScalarResult();
var_dump($totalRowsTable);

$random = (int)max(0, rand(1, $totalRowsTable));
var_dump($random);

$qb = $this->createQueryBuilder('article')
     ->andWhere("article.id IN (:id)")
     ->setParameter('id', $random)
     ->setMaxResult(1);
return $qb->getQuery()->getResult();

I'm not understanding why the function returns zero. How can I get a random record from the database in a more correct way?

Does this answer your question? [Best way to select random rows PostgreSQL](https://stackoverflow.com/questions/8674718/best-way-to-select-random-rows-postgresql) — ArSeN, Dec 24 '20 at 20:49
The number of entries does not necessarily match the IDs of your data rows. i.e. your row Ids could start at 10000 but you only have 50 entries. You'd never get a result then. — ArSeN, Dec 24 '20 at 20:51

score 1 · Accepted Answer · answered Dec 25 '20 at 09:32

$idArticle = $this->createQueryBuilder('article')
            ->select('MIN(article.id)', 'MAX(article.id)')
            ->getQuery()
            ->getOneOrNullResult();
        $random = rand($idArticle[1], $idArticle[2]);

        return $this->createQueryBuilder('article')
            ->where('article.id >= :randomId')
            ->setParameter('randomId', $random)
            ->setMaxResults(1)
            ->getQuery()
            ->getResult();

score 0 · Answer 2 · answered Dec 25 '20 at 11:03

I thinks that could be correct answer for your questions.

First off all we have to calculate number of rows in table by executing followed code:

$queryBuilder = $this->createQueryBuilder('article');
$count = $queryBuilder
    ->select('COUNT(article.id)')
    ->getQuery()
    ->getSingleScalarResult();

Next, we have to calculate random value from 0 to $count. But functions rand or even mt_rand are not enought secured to use becouse of:

Caution This function does not generate cryptographically secure values, and should not be used for cryptographic purposes. If you need a cryptographically secure value, consider using random_int(), random_bytes(), or openssl_random_pseudo_bytes() instead.

Insted of that we should use for example function random_int. So, to choose secured random values we implements code below:

$offset = random_int(0, max(0, $count - 1));

And at least, we could retrive random row record for table:

return $this->createQueryBuilder('article')
    ->setFirstResult($offset)
    ->setMaxResult(1)
    ->getOneOrNullResult;

how to get random entry from db?

2 Answers2