Project

General

Profile

Actions

Feature #14026

closed

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

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

Status:
Rejected
Priority:
Should have
Assignee:
-
Category:
Extbase
Target version:
Start date:
2011-03-24
Due date:
% Done:

0%

Estimated time:
PHP Version:
Tags:
Complexity:
Sprint Focus:

Description

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.

See:
Tx_Extbase_Persistence_Storage_Typo3DbBackend::parseOrderings()


Related issues 1 (0 open1 closed)

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

Actions
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?

[1] http://www.day.com/specs/jcr/1.0/6.6.3.5_Ordering_Specifier.html
[2] http://www.petefreitag.com/item/466.cfm

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).
http://www.doctrine-project.org/jira/browse/DDC-950

And this is an easy way to implement it in a custom query (german)
http://typo3blogger.de/random-record-repository/

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 http://typo3blogger.de/random-record-repository/ - 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 about 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.

<?php

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 <daniel.siepmann@typo3.org>
 * @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 <daniel.siepmann@typo3.org>
     */
    protected function parseOrderings( array $orderings, Qom\SourceInterface $source, array &$sql ) {
        parent::parseOrderings( $orderings, $source, $sql );
        if (count( $sql['orderings'] ) === 0) {
            return;
        }

        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' )
            ->registerImplementation(
                'TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser',
                'CUSTOM\CustomHelpers\Persistence\Generic\Storage\CustomDbQueryParser'
            );
}

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 almost 9 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 INSTALL.md 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';
                    break;
                case QueryInterface::ORDER_DESCENDING:
                    $order = 'DESC';
                    break;
                case QueryInterface::ORDER_RAND:
                    // I think there is an flag anywhere?!
                    switch ($USEDDBMS) {
                        case 'MYSQL':
                            $order = 'RAND()';
                            break;
                        case 'POSTGRE':
                            $order = 'RANDOM()';
                            break;
                    }
                    $tableName = '';
                    $columnName = '';
                    break;
                default:
                    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 https://review.typo3.org/49041

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 https://github.com/doctrine/doctrine2/issues/5479

Actions

Also available in: Atom PDF