Symfony 2: Doctrine 2 get random entites (MySQL)

Often during the process of developing projects on the Symfony 2 framework and Doctrine 2 ORM many web applications developers face the problem of getting random records from a MySQL database. Most developers know that this issue is not new, and all solutions that already exist have been known for a long time. But you can hardly find an implementation of any of them on the Symfony 2 framework and Doctrine 2 in the Internet. In this post I want to propose you , in my opinion, the most simple way of selecting random records from MySQL by means of the function RAND.

Attention! This method is not productive. I ask you to take it into account in your applications and not to use this method of obtaining a random record from MySQL in bulk data.

Describe RAND() function

As ORM Doctrine 2 does not have support for RAND() function in MySQL, you must describe this function firstly. For this purpose create a class which will be used as the description of RAND() DQL. Listing class code is given below:

<?php
 
namespace Acme\DemoBundle\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()';
    }
}

Then you need to connect this description to the list of functions Doctrine 2 ORM. This requires changing the Symfony 2 application configuration which is stored, as a rule, in the file app/config/config.yml, and add connection of the RAND() function description into the configuration of the ORM Doctrine 2. Listing of the configuration ORM Doctrine 2 is given below:

… 

doctrine:
    orm:
        dql:
            numeric_functions:
                Rand: Acme\DemoBundle\DQL\RandFunction

…

Creation of the repository and method for getting random records

In this guide, I’m not going to describe what repositories are and what they are used for, the official documentation for Symfony 2 framework or Doctrine 2 ORM can tell you about it better than me. So, I’ll just give you a listing of the repository with the implementation of the method of random selection of entries below:

<?php
 
namespace Acme\DemoBundle\Entity\Repository;
 
use Doctrine\ORM\EntityRepository;
 
class SomeRepository extends EntityRepository
{
    /**
     * Get random entities
     *
     * @param int $count Entities count, default is 10
     *
     * @return array
     */
    public function getRandomEntities($count = 10)
    {
        return  $this->createQueryBuilder('q')
            ->addSelect('RAND() as HIDDEN rand')
            ->addOrderBy('rand')
            ->setMaxResults($count)
            ->getQuery()
            ->getResult();
    }
}

As you can see, the method of obtaining random entities is quite simple. If you ask me why I don’t use call of the function RAND() in ORDER BY, I can tell that I don’t do this because DQL does not support the use of functions in ORDER BY, that’s why the generation of random values was carried in SELECT query specifying alias rand, further the name of an alias is used in ORDER BY.

Conclusion

The article shows a simple way of implementation of the ability to retrieve random records from a MySQL project using Symfony2 framework and Doctrine 2 ORM. I hope the method described by me will be useful for you.

About the author

Sergey Novichkov

Sergey is Head of PHP development at ISS Art. LLC.

  • 0x1gene

    Thank you very much ! I found your article on your blog but in english it’s much easier to understand… It is indeed not very well documented and your solution is by far the best I came across so far.

    • Sergey

      Thanks for your comment. Glad that you found the article useful.

  • dannyalfonzo

    Hi, how i can use this repository un a query on some entity? is not clear to me

    • Thanks for your question. Sorry for our late response. You can use MySQL RAND() function to get random entities in your entity repository class. For example, your goal is to get 3 random User records from the database. Here are steps to solve this issue: (I assume you’ve already implemented RandFunction class and defined Rand function in your config.yml as described in the article)
      1. Create repository for User entity.

      < ?php
      
      namespace AppBundle\Entity\Repository;
      
      use Doctrine\ORM\EntityRepository;
      
      class UserRepository extends EntityRepository
      {
      }
      

      2. Link UserRepository class to your User entity

      /**
       * @ORM\Entity(repositoryClass="AppBundle\Entity\Repository\UserRepository")
       * @ORM\Table(name="user")
       */
      class User
      {
      

      3. Implement getRandomUsers() function in UserRepository

          /**
           * @param int $count
           * @return array
           */
          public function getRandomUsers($count = 10)
          {
              return  $this->createQueryBuilder('u')
                  ->addSelect('RAND() as HIDDEN rand')
                  ->addOrderBy('rand')
                  ->setMaxResults($count)
                  ->getQuery()
                  ->getResult();
          }
      

      4. Use getRandomUsers() function in your controller

      < ?php
      
      namespace AppBundle\Controller;
      
      use Symfony\Bundle\FrameworkBundle\Controller\Controller;
      use Symfony\Component\HttpFoundation\Response;
      use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
      
      class DefaultController extends Controller
      {
          /**
           * @Route(path="/", name="homepage")
           *
           * @return Response
           */
          public function indexAction()
          {
              $users = $this->getDoctrine()->getRepository('AppBundle:User')->getRandomUsers(3);
      
              return $this->render('AppBundle:Default:index.html.twig', [
                  'users' => $users,
              ]);
          }
      }
      

      If you have any questions, please feel free to ask.

  • Pingback: Symfony | Pearltrees()

  • SD Lutonda II

    Thanks very much!
    but I ther order by parametter worked with me with ‘RAND()’

    • Svetlana Stasilovich

      Thanks! OK, I understand. Can you send more details at info@issart.com?