Using RAND() or Other Non-Supported Functions

With a Subscription, click any sentence in the script to jump to that part of the video!

For the heck of it, let's randomize the order of the fortunes on a page. Try this category, which has 4.

Start by opening up FortuneController and finding showCategory(). Right now, we're querying for the category in the normal way. Then, in our template, we loop over category.fortuneCookies.

Change the query back to ->findWithFortunesJoin(), which lives over here in CategoryRepository. Remember: this joins over to FortuneCookie and selects that data, solving our N+1 problem.

            
            52 lines
            
            src/Controller/FortuneController.php
        
                                                    ... lines 1 - 13
                            
            class FortuneController extends AbstractController
        
            {
        
                                                    ... lines 16 - 35
                            
                public function showCategory(int $id, CategoryRepository $categoryRepository, FortuneCookieRepository $fortuneCookieRepository): Response
        
                {
        
                    $category = $categoryRepository->findWithFortunesJoin($id);
        
                                                    ... lines 39 - 49
                            
                }
        
            }

Now that we're doing this, we can also control the order. Say ->orderBy('RAND()', Criteria::ASC). We're only querying for one Category... but this will control the order of the related fortune cookies as well... which we'll see when we loop over them.

            
            121 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 6
                            
            use Doctrine\Common\Collections\Criteria;
        
                                                    ... lines 8 - 18
                            
            class CategoryRepository extends ServiceEntityRepository
        
            {
        
                                                    ... lines 21 - 54
                            
                public function findWithFortunesJoin(int $id): ?Category
        
                {
        
                    return $this->addFortuneCookieJoinAndSelect()
        
                                                    ... lines 58 - 59
                            
                        ->orderBy('RAND()', Criteria::ASC)
        
                                                    ... lines 61 - 62
                            
                }
        
                                                    ... lines 64 - 119
                            
            }

Pretty cool! If we try this... error?

Expected known function, got RAND

Wait... RAND is a known MySQL function. So... why doesn't it work? Ok, Doctrine supports a lot of functions inside DQL, but not everything. Why? Because Doctrine is designed to work with many different types of databases... and if only one or some databases support a function like RAND, then Doctrine can't support it. Fortunately, we can add this function or any custom function we want ourselves or, really, via a library.

Search for the beberlei/doctrineextensions library. This is awesome. It allows us to add a bunch of different functions to multiple database types. Go down here and grab the composer require line... but we don't need the dev-master part. Run that!

composer require beberlei/doctrineextensions

Installing this doesn't change anything in our app... it just adds a bunch of code that we can activate for any functions that we want. To do that, back over in config/packages/doctrine.yaml, somewhere under orm, say dql. There are a bunch of different categories under here, which you can read more about in the documentation. In our case, we need to add numeric_functions along with the name of the function, which is rand. Set this to the class that will let Doctrine know what to do: DoctrineExtensions\Query\Mysql\Rand.

            
            54 lines
            
            config/packages/doctrine.yaml
        
            doctrine:
        
                                                    ... lines 2 - 7
                            
                orm:
        
                                                    ... lines 9 - 24
                            
                    dql:
        
                        numeric_functions:
        
                            rand: DoctrineExtensions\Query\Mysql\Rand
        
                                                    ... lines 28 - 54

You definitely don't have to take my word about how this should be set up. Over in the documentation... there's a "config" link down here... and if you click on mysql.yml, you can see that it describes all the different things you can do and how to activate them.

I'll close that up... refresh, and... got it! Each time we refresh, the results are coming back in a different order.

Okay, one more topic team! Let's finish with a complex groupBy() situation where we select some objects and some extra data all at once.

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": ">=8.1",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "beberlei/doctrineextensions": "^1.3", // v1.3.0
        "doctrine/doctrine-bundle": "^2.7", // 2.9.1
        "doctrine/doctrine-migrations-bundle": "^3.2", // 3.2.2
        "doctrine/orm": "^2.13", // 2.15.1
        "symfony/asset": "6.2.*", // v6.2.7
        "symfony/console": "6.2.*", // v6.2.10
        "symfony/dotenv": "6.2.*", // v6.2.8
        "symfony/flex": "^2", // v2.2.5
        "symfony/framework-bundle": "6.2.*", // v6.2.10
        "symfony/proxy-manager-bridge": "6.2.*", // v6.2.7
        "symfony/runtime": "6.2.*", // v6.2.8
        "symfony/twig-bundle": "6.2.*", // v6.2.7
        "symfony/webpack-encore-bundle": "^1.16", // v1.16.1
        "symfony/yaml": "6.2.*" // v6.2.10
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.4", // 3.4.4
        "symfony/maker-bundle": "^1.47", // v1.48.0
        "symfony/stopwatch": "6.2.*", // v6.2.7
        "symfony/web-profiler-bundle": "6.2.*", // v6.2.10
        "zenstruck/foundry": "^1.22" // v1.32.0
    }
}

Edit on GitHub

TRACK

Symfony 6 >

COURSE

Go Pro with Doctrine Queries

Using RAND() or Other Non-Supported Functions

Keep on Learning!

Leave a comment!

What PHP libraries does this tutorial use?

	... lines 1 - 13
	class FortuneController extends AbstractController
	{
	... lines 16 - 35
	public function showCategory(int $id, CategoryRepository $categoryRepository, FortuneCookieRepository $fortuneCookieRepository): Response
	{
	$category = $categoryRepository->findWithFortunesJoin($id);
	... lines 39 - 49
	}
	}

	... lines 1 - 6
	use Doctrine\Common\Collections\Criteria;
	... lines 8 - 18
	class CategoryRepository extends ServiceEntityRepository
	{
	... lines 21 - 54
	public function findWithFortunesJoin(int $id): ?Category
	{
	return $this->addFortuneCookieJoinAndSelect()
	... lines 58 - 59
	->orderBy('RAND()', Criteria::ASC)
	... lines 61 - 62
	}
	... lines 64 - 119
	}

	doctrine:
	... lines 2 - 7
	orm:
	... lines 9 - 24
	dql:
	numeric_functions:
	rand: DoctrineExtensions\Query\Mysql\Rand
	... lines 28 - 54