Feature #14026


Add new ordering "RAND()" to TYPO3 DB Backend

Added by Kai Vogel over 13 years ago. Updated almost 8 years ago.

Should have
Target version:
Start date:
Due date:
% Done:


Estimated time:
PHP Version:
Sprint Focus:


Currently the only allowed sort modes are ascending (ASC) and descending (DESC), but there is no way to order by RAND, which will return a random result set. The only possibility to do that is, to create a native SQL statement and lose the abstraction from a concrete storage backend.


Related issues 1 (0 open1 closed)

Related to TYPO3 Core - Feature #70964: Allow sorting = rand() for special = categories (HMENU)Closed2015-10-23

Actions #2

Updated by Ingo Pfennigstorf almost 12 years ago

  • Status changed from New to Needs Feedback
  • Target version deleted (Extbase 1.4)

As the JCR specification that we follow here [1] does not have a RAND or other random ordering and ORDER BY RAND is very MySQL specific (see [2]) I would suggest to reject this feature. What do you think?


Actions #3

Updated by Stefan Neufeind almost 12 years ago

I agree we need some solution that can be used across various DBMS. Thanks for the links.

But we should have random-sorting in some way. So I agree with you that simply using RAND and forgetting about other DBMS is not a solution. But I disagree with you in regards to rejecting this feature-request itself.

Actions #4

Updated by Ingo Pfennigstorf almost 12 years ago

This is how Doctrine (FLOW3) solved it (not).

And this is an easy way to implement it in a custom query (german)

Actions #5

Updated by Stefan Neufeind almost 12 years ago

Yey, so that's what an ORM is for: Telling you it doesn't care about the database-specific stuff and only leaves creating a custom (DB-dependent solution) as your only option.

The custom repository works in a way. But if you need more rows it even gets a bit more hacky. (Yes you could do a fetch where uid in (...) and shuffle them inside php). So exactly that "custom solution" that everybody needing a RAND will have to write himself is not really an option imho.

Now if Doctrine doesn't want to help, and if Extbase needs to find a way to do it that FLOW3 will be fine with as well, what's our option? Put database-dependent stuff inside Extbase/FLOW3 since Doctrine doesn't want to care about it? Hmm ...

Actions #6

Updated by Anja Leichsenring about 11 years ago

  • Target version set to Extbase 6.3
Actions #7

Updated by Maximilian Kalus about 10 years ago

I agree with Stefan: In real world scenarios, sometimes you would need a rand-function. Simply telling me, that your ORM does not support it, does not help.

Up to TYPO3 6.1, one could work with a simple workaround (described in - my comment from 27.03.2014 ). In 6.2, buildQuery is not exposed any more, so you cannot hack the SQL statement any more. Honestly, this is a negative development. I can understand convertig statements to work smoothly with models, etc., but to close off query building completely to a developer is a grevious mistake! In my 10 years of TYPO3 developing, I often had to implement very specific queries for customers (from imports, weird sources, etc.), randomized queries being a relatively common task (and no, shuffling 5000 records in PHP is not an option, sorry!). In TYPO3 6.2, my only option for this is using plain old $GLOBALS['TYPO3_DB'] - seriously?

Please fix this!

Actions #8

Updated by Daniel Siepmann almost 10 years ago

Another workaround is to extend the Typo3DbQueryParser like the following.

It's just a workaround that allows rand() but can be extended e.g. to support any function that configured.


namespace CUSTOM\CustomHelpers\Persistence\Generic\Storage;

use TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser;
use TYPO3\CMS\Extbase\Persistence\Generic\Qom;

 * Extend Typo3DbQueryParser to support some MySQL features.
 * As we only use MySQL we can use MySQL features inside our queries,
 * even if we build them using the query factory of Extbase.
 * @author Daniel Siepmann <>
 * @since 6.2
class CustomDbQueryParser extends Typo3DbQueryParser {

     * Extend default parsing to allow rand() explicity.
     * @param array $orderings An array of orderings (Qom\Ordering)
     * @param Qom\SourceInterface $source The source
     * @param array &$sql The query parts
     * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException
     * @return void
     * @author Daniel Siepmann <>
    protected function parseOrderings( array $orderings, Qom\SourceInterface $source, array &$sql ) {
        parent::parseOrderings( $orderings, $source, $sql );
        if (count( $sql['orderings'] ) === 0) {

        foreach ($sql['orderings'] as $orderingKey => $ordering) {
            $positionOfRandom = stripos( $sql['orderings'][0], 'rand()' );
            if ($positionOfRandom !== false) {
                $sql['orderings'][$orderingKey] = substr( $ordering, $positionOfRandom );


Just register the cutom parser in the ext_localconf.php:

if (TYPO3_branch !== '4.7') {
    // Overwrite the Dependency Injection for Typo3DbQueryParser.
    // @since 6.2
    \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance( '\\TYPO3\\CMS\\Extbase\\Object\\Container\\Container' )

Actions #9

Updated by Alexander Opitz almost 10 years ago

  • Project changed from 534 to TYPO3 Core
  • Category changed from Extbase: Generic Persistence to Extbase
  • Status changed from Needs Feedback to New
  • Target version changed from Extbase 6.3 to 7.0
Actions #10

Updated by Mathias Schreiber over 9 years ago

  • Target version changed from 7.0 to 7.1 (Cleanup)
Actions #11

Updated by Benni Mack about 9 years ago

  • Target version changed from 7.1 (Cleanup) to 7.4 (Backend)
Actions #12

Updated by Susanne Moog almost 9 years ago

  • Target version changed from 7.4 (Backend) to 7.5
Actions #13

Updated by Benni Mack over 8 years ago

  • Target version changed from 7.5 to 8 LTS
Actions #14

Updated by Marc Horst almost 8 years ago

I tried to implement the random sort.
It was not that hard, but what is with the requirement to work with other dbms?

I searched in the for different dbms options, but it only says "drop-in" compatible systems.

I always use mysql. Is there an option in LocalConfiguration.php if something else is used?
We could do something like this:

switch ($order) {
                case QueryInterface::ORDER_ASCENDING:
                    $order = 'ASC';
                case QueryInterface::ORDER_DESCENDING:
                    $order = 'DESC';
                case QueryInterface::ORDER_RAND:
                    // I think there is an flag anywhere?!
                    switch ($USEDDBMS) {
                        case 'MYSQL':
                            $order = 'RAND()';
                        case 'POSTGRE':
                            $order = 'RANDOM()';
                    $tableName = '';
                    $columnName = '';
                    throw new \TYPO3\CMS\Extbase\Persistence\Generic\Exception\UnsupportedOrderException('Unsupported order encountered.', 1242816074);

Actions #15

Updated by Gerrit Code Review almost 8 years ago

  • Status changed from New to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at

Actions #16

Updated by Wouter Wolters almost 8 years ago

  • Status changed from Under Review to Rejected

I reject this feature. The core switches to the doctrine database layer and they don't support RAND.
More information about why they won't support it can be found here


Also available in: Atom PDF