Randomizing MySQL query results

Published on 2020-01-27 • Modified on 2020-01-27

In this snippet, we will see how to randomize MySQL query results. On this website, I am using this functionality for the random snippet feature. In this case, I only need to have one result so I add the LIMIT 1 SQL clause. On the run page, refresh the page to verify that the snippet ID changes (or not, it's random!). Be careful not to use this on large results sets (more than 10000 rows) or you may have performance problems.


<?php declare(strict_types=1);

// src/Controller/Snippet/Snippet74Trait.php

namespace App\Controller\Snippet;

use App\Repository\ArticleTypeRepository;
use Doctrine\DBAL\Connection;

/**
 * I am using a PHP trait in order to isolate each snippet in a file.
 * This code should be called from a Symfony controller extending AbstractController (as of Symfony 4.2)
 * or Symfony\Bundle\FrameworkBundle\Controller\Controller (Symfony <= 4.1).
 * Services are injected in the main controller constructor.
 *
 * @property Connection $connection
 */
trait Snippet74Trait
{
    public function snippet74(): void
    {
        $sql = sprintf('SELECT * FROM article WHERE type_id = %d ORDER BY RAND() LIMIT 1', ArticleTypeRepository::TYPE_SNIPPET);
        $article = $this->connection->fetchAll($sql)[0] ?? [];

        echo 'Random snippet id: '.$article['id'];

        // That's it! 😁
    }
}

 Run this snippet  ≪ showUnitTestButtonLabel ≫  More on Stackoverflow   Read the doc  More on the web

<?php declare(strict_types=1);

namespace App\Tests\Controller\Snippets;

use App\Repository\ArticleTypeRepository;
use Symfony\Bundle\FrameworkBundle\Test\KernelTestCase;

/**
 * @covers Snippet74Trait
 */
final class Snippet74Test extends KernelTestCase
{
    private $connection;

    protected function setUp(): void
    {
        self::bootKernel();
        $this->connection = self::$kernel->getContainer()->get('doctrine.dbal.default_connection');
    }

    /**
     * @covers Snippet74Trait::snippet74
     */
    public function testSnippet74(): void
    {
        $sql = sprintf('SELECT * FROM article WHERE type_id = %d ORDER BY RAND() LIMIT 1', ArticleTypeRepository::TYPE_SNIPPET);
        $article = $this->connection->fetchAll($sql)[0] ?? [];
        $this->assertIsArray($article);
        $this->assertSame((int) $article['type_id'], ArticleTypeRepository::TYPE_SNIPPET);
    }
}