Issue Feature Closed

#32051Concat ordering for Database request

Anonymous 11/24/11, 13:15 updated 03/31/26, 09:00

Description

Hi there

it would be nice to be able to made “concat orderings” over multiple fields with database.

I’d imagine this looking like this:

$query = $this->createQuery();

$query->setOrderings(
array(
'CONCAT(field1,field2,field3) as 'fieldx' => Tx_Extbase_Persistence_QueryInterface::ORDER_DESCENDING
)
);

The code must then check with preg_match if CONCAT\((.*)\) as ([^\s]+) is set.

I’m probably going to implement something like this by my self and I’ll post it as soon as I’ve got something that works.

Greets Tizian

Reviews 4

History 1528

Show 3 earlier messages
  1. Anonymous commented and changed done 0 % 90 % <span data-processor="relativetime" data-value="2011-11-25T16:18:44+00:00" > 11/25/11, 16:18 </span>

    Hello there

    I as promised I made a patch and here is how it works:

    New Version of Tx_Extbase_Persistence_Storage_Typo3DbBackend::parseOrderings

        /**
         * Transforms orderings into SQL.
         *
         * @param array $orderings An array of orderings (Tx_Extbase_Persistence_QOM_Ordering)
         * @param Tx_Extbase_Persistence_QOM_SourceInterface $source The source
         * @param array &$sql The query parts
         * @return void
         */
        protected function parseOrderings(array $orderings, Tx_Extbase_Persistence_QOM_SourceInterface $source, array &$sql) {
            foreach ($orderings as $propertyName => $order) {
                switch ($order) {
                    case Tx_Extbase_Persistence_QOM_QueryObjectModelConstantsInterface::JCR_ORDER_ASCENDING: // Deprecated since Extbase 1.1
                    case Tx_Extbase_Persistence_QueryInterface::ORDER_ASCENDING:
                        $order = 'ASC';
                        break;
                    case Tx_Extbase_Persistence_QOM_QueryObjectModelConstantsInterface::JCR_ORDER_DESCENDING: // Deprecated since Extbase 1.1
                    case Tx_Extbase_Persistence_QueryInterface::ORDER_DESCENDING:
                        $order = 'DESC';
                        break;
                    default:
                        throw new Tx_Extbase_Persistence_Exception_UnsupportedOrder('Unsupported order encountered.', 1242816074);
                }
                if ($source instanceof Tx_Extbase_Persistence_QOM_SelectorInterface) {
                    $className = $source->getNodeTypeName();
                    $tableName = $this->dataMapper->convertClassNameToTableName($className);
                    while (strpos($propertyName, '.') !== FALSE) {
                        $this->addUnionStatement($className, $tableName, $propertyName, $sql);
                    }
                } elseif ($source instanceof Tx_Extbase_Persistence_QOM_JoinInterface) {
                    $tableName = $source->getLeft()->getSelectorName();
                }
    
                $aConcatFields = array();
                if(preg_match('/(concat)\(([^)]*)\)\s*as\s+([^\s]+)/i', $propertyName, $aConcatFields)) {
                    $currentTable = $sql['fields'];
    
                    // just get the first table name to simulate a proper table field
                    foreach($currentTable as $table => $fields) {
                        $sql['fields'][$table] = $fields . ',' . $aConcatFields[1] . '('.$aConcatFields[2].') as ' . $aConcatFields[3];
                    }
                    // $sql['fields'] = $aConcatFields[1] . '('.$aConcatFields[3].') as ' . $aConcatFields[4];
                    $columnName = $aConcatFields[3];
    
                    $tableName = '';
    
                } else {
                    $columnName = $this->dataMapper->convertPropertyNameToColumnName($propertyName, $className);
                }
    
                if (strlen($tableName) > 0) {
                    $sql['orderings'][] = $tableName . '.' . $columnName . ' ' . $order;
                } else {
                    $sql['orderings'][] = $columnName . ' ' . $order;
                }
            }
        }

    The counting method has to be modified as well (Tx_Extbase_Persistence_Storage_Typo3DbBackend::getObjectCountByQuery):

        /**
         * Returns the number of tuples matching the query.
         *
         * @param Tx_Extbase_Persistence_QOM_QueryObjectModelInterface $query
         * @return integer The number of matching tuples
         */
        public function getObjectCountByQuery(Tx_Extbase_Persistence_QueryInterface $query) {
            $constraint = $query->getConstraint();
            if($constraint instanceof Tx_Extbase_Persistence_QOM_StatementInterface) {
                throw new Tx_Extbase_Persistence_Storage_Exception_BadConstraint('Could not execute count on queries with a constraint of type Tx_Extbase_Persistence_QOM_StatementInterface', 1256661045);
            }
            $parameters = array();
            $statementParts = $this->parseQuery($query, $parameters);
            // if limit is set, we need to count the rows "manually" as COUNT(*) ignores LIMIT constraints
            if (!empty($statementParts['limit'])) {
                $statement = $this->buildQuery($statementParts, $parameters);
                $this->replacePlaceholders($statement, $parameters);
                $result = $this->databaseHandle->sql_query($statement);
                $this->checkSqlErrors($statement);
                $count = $this->databaseHandle->sql_num_rows($result);
            } else {
                $statementParts['fields'] = array('COUNT(*)');
                // Backup the ordering statement
                $oderings = $statementParts['orderings'];
                // remove it since for count we do not need any ordering
                $statementParts['orderings'] = null;
                // make the query
                $statement = $this->buildQuery($statementParts, $parameters);
                // put back the orderings to the statemet parts
                $statementParts['orderings'] = $orderings;
                unset($orderings);
                $this->replacePlaceholders($statement, $parameters);
                $result = $this->databaseHandle->sql_query($statement);
                $this->checkSqlErrors($statement);
                $rows = $this->getRowsFromResult($query->getSource(), $result);
                $count = current(current($rows));
            }
            $this->databaseHandle->sql_free_result($result);
            return $count;
        }

    Greetings Tizian

  2. Alexander Schnitzler commented <span data-processor="relativetime" data-value="2012-11-18T19:15:34+00:00" > 11/18/12, 19:15 </span>

    Hey,
    thanks for the code but would you mind creating a real patch for gerrit?

  1. Alexander Opitz commented and changed status New Needs Feedback <span data-processor="relativetime" data-value="2014-09-19T09:11:32+00:00" > 09/19/14, 09:11 </span>

    Hi,

    was this issue fixed or does it still exists?

  2. Tizian Schmidlin commented <span data-processor="relativetime" data-value="2014-09-19T09:19:32+00:00" > 09/19/14, 09:19 </span>

    Hello,

    the problem still persists I think. I’ll try to write a patch and challenge it against Gerrit but since 6.2 has come out I’ll have to rewrite it for the new version first.

  3. Tizian Schmidlin commented <span data-processor="relativetime" data-value="2014-12-19T09:26:58+00:00" > 12/19/14, 09:26 </span>

    I given this some thought lately since I stumbled across a similar issue lately.

    Maybe the solution would be a set of reserved words that cannot appear in the selected fields when trying to count, so if such field appears, the whole field part, then it would be simply rewritten to COUNT(*).

    Sadly I hadn’t any time yet to write a patch for this, I hope I’ll be able to during the holidays.

    Best Regards
    Tizian

  4. Mathias Schreiber commented <span data-processor="relativetime" data-value="2018-02-03T22:56:39+00:00" > 02/03/18, 22:56 </span>

    hey Tizian,

    did you get holidays in the last 3 years? :)

  5. Tizian Schmidlin commented <span data-processor="relativetime" data-value="2018-11-13T14:51:24+00:00" > 11/13/18, 14:51 </span>

    Fair enough, I’ll try to make it into core this time, now that I’m all set up, this should not take too long :-)

  6. Gerrit Code Review pushed patch set 1 for master to change 61443 status New Under Review <span data-processor="relativetime" data-value="2019-08-05T16:09:01+00:00" > 08/05/19, 16:09 </span>

  7. Benni Mack commented and changed status Under Review Resolved done 90 % 100 % <span data-processor="relativetime" data-value="2026-02-08T09:55:11+00:00" > 02/08/26, 09:55 </span>

    Applied in changeset commit:32737dc4ae9a97ad860d616deec0a4196ba1dc5f.