CODEWITHSUNDEEP
phppostgresqldoctrine-orm
user8239381
user8239381

Reputation:

how to get random entry from db?

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?

Upvotes: 0

Views: 103

Answers (2)

VirCom
VirCom

Reputation: 3622

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;

Upvotes: 0

alex
alex

Reputation: 608

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

Upvotes: 1

Related Questions