TYPO3 8.7 Query Sorting with Flexform uid´s

Question

I got a problem with many TYPO3 extensions with ordering query results by Uid´s which come from a flexform Plugin setting in the Backend. I try to create a query what gives me the result uid´s in the same order like the flexform is from the Plugin setting. Like i choose data.uid 5 7 and 3 and my query results give me those in this order.

For Example:

Siteinfo:

PHP 7.0
TYPO3 8.7
mariadb:10.1
Debian server

This Function is called from the Controller.

$partners = $this->partnerRepository->findByUids($this->settings['showMainSponsor']);

in $this->settings['showMainSponsor'] is the value ="3, 4 ,1".

These are the Uid´s from the selected area in the TYPO3 Plugin Settings.

The repository function "findByUids" looks like this.

public function findByUids($uids){

    if(!isset($uids) || empty($uids)){
        return NULL;
    }

    $uidListString = $uids;
    if(!is_array($uids)){
        $uidListString = explode(',', $uids);
    }

    $query = $this->createQuery();
    $query->getQuerySettings()->setRespectStoragePage(FALSE);

    //here i set the orderings
    $orderings = $this->orderByField('uid', $uidListString);
    $query->setOrderings($orderings);

    $query->matching(
        $query->logicalAnd(
            $query->in('uid', $uidListString)
        )
    );



    return $query->execute();
}

A function called "orderByField" is called here which sets all the orderings.

/**
 * @param string $field
 * @param array $values
 *
 * @return array
 */
protected function orderByField($field, $values) {
    $orderings = array();
    foreach ($values as $value) {
        $orderings["$field={$value}"] =  \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING;
    }
    return $orderings;
}

These method of ordering the queryresult by the given uid list from the Flexform works in TYPO3 6.2 and 7.6. Now i tried to attach this extension to a TYPO3 8.6 project but this method doesnt work anymore. I tried to debug it and looked up in the query. There i found what broke this query. The query which doesnt work looks like this:

SELECT `tx_partner_domain_model_partner`.* FROM `tx_partner_domain_model_partner` `tx_partner_domain_model_partner` WHERE (`tx_partner_domain_model_partner`.`uid` IN (3, 4, 1)) AND (`tx_partner_domain_model_partner`.`sys_language_uid` IN (0, -1)) AND ((`tx_partner_domain_model_partner`.`deleted` = 0) AND (`tx_partner_domain_model_partner`.`t3ver_state` <= 0) AND (`tx_partner_domain_model_partner`.`pid` <> -1) AND (`tx_partner_domain_model_partner`.`hidden` = 0) AND (`tx_partner_domain_model_partner`.`starttime` <= 1506603780) AND ((`tx_partner_domain_model_partner`.`endtime` = 0) OR (`tx_partner_domain_model_partner`.`endtime` > 1506603780))) ORDER BY `tx_partner_domain_model_partner`.`uid=3` DESC, `tx_partner_domain_model_partner`.`uid=4` DESC, `tx_partner_domain_model_partner`.`uid=1` DESC

I tried this on my DBMS and it failed. The reason are the last 3 statements.

`tx_partner_domain_model_partner`.`uid=3` DESC, `tx_partner_domain_model_partner`.`uid=4` DESC, `tx_partner_domain_model_partner`.`uid=1` DESC

TYPO3 escaped the uid with `` like

 `tx_partner_domain_model_partner`.`uid=4` DESC

if we do the call like this without these `` arround the uid=3 ..

`tx_partner_domain_model_partner`.uid=3 DESC, `tx_partner_domain_model_partner`.uid=4 DESC, `tx_brapartner_domain_model_partner`.uid=1 DESC

it works fine. Maybe there is a security reason why TYPO3 does this on his newest version but i dont find any other good solution for this basic case. At the moment i got a foreach where i query every uid by his own by findByUid but this dont seem to me like a "best practice" way. Does anybody got a cleaner way for this case of getting data from the db? Or maybe this is a Bug ?

I Hope someone can help me.

best regards

Fanor

score 3 · Answer 1 · answered Jun 12 '19 at 12:16

Without Overwrite:

Clear default Orderings:

$query->setOrderings(array());

Transform your QueryBuilder to the new Doctrine QB:

/** @var Typo3DbQueryParser $queryParser */
$queryParser = $this->objectManager->get(Typo3DbQueryParser::class);
/** @var QueryBuilder $doctrineQueryBuilder */
$doctrineQueryBuilder = $queryParser->convertQueryToDoctrineQueryBuilder($query);

Add the UIDs via concreteQb

$concreteQb = $doctrineQueryBuilder->getConcreteQueryBuilder();
foreach ($uidList as $uid) {
  $concreteQb->addOrderBy("$key={$uid}", QueryInterface::ORDER_DESCENDING);
}

Get the mapped results:

/** @var DataMapper $dataMapper */
$dataMapper = $this->objectManager->get(DataMapper::class);
return $dataMapper->map(YourDataClass::class, $$doctrineQueryBuilder->execute()->fetchAll());

This is the best answer as you can "prepare" the query using the common query object (do all the constraint stuff) and at the end of your repository query just pass it to doctrine to do the ordering stuff. — JKB, Jan 27 '20 at 14:03

Wolfgang · Answer 2 · 2018-01-22T14:19:49.113

I think the problem is in \TYPO3\CMS\Core\Database\Query\QueryBuilder::orderBy where the fieldName gets quoted. You could overwrite this class and split the fieldName by = and build a quoted string and intval() the remaining string like:

public function orderBy(string $fieldName, string $order = null): QueryBuilder
{
    if (strpos($fieldName, '=') !== false) {
        list($field, $value) = GeneralUtility::trimExplode('=', $fieldName);
        $field = $this->connection->quoteIdentifier($field);
        $value = intval($value);
        $fieldName = $field . $value;
    }
    else {
        $fieldName = $this->connection->quoteIdentifier($fieldName);
    }
    $this->concreteQueryBuilder->orderBy($fieldName, $order);

    return $this;
}

score 2 · Answer 3 · answered Sep 15 '21 at 09:06

public function findByUidList($uidList)
{
    $uids = GeneralUtility::intExplode(',', $uidList, true);
    if ($uidList === '' || count($uids) === 0) {
        return [];
    }

    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->getTableName());
    $queryBuilder->setRestrictions(GeneralUtility::makeInstance(FrontendRestrictionContainer::class));

    $records = $queryBuilder
        ->select('*')
        ->from($this->getTableName())
        ->where($queryBuilder->expr()->in('uid', $uids))
        ->add('orderBy', 'FIELD('.$this->getTableName().'.uid,' . implode(',', $uids) . ')')
        ->execute()
        ->fetchAll();

    $objectManager = GeneralUtility::makeInstance(ObjectManager::class);
    $dataMapper = $objectManager->get(DataMapper::class);
    $result = $dataMapper->map($this->objectType, $records);

    return $result;
}

/**
 * Return the current table name
 *
 * @return string
 */
protected function getTableName()
{
    $objectManager = GeneralUtility::makeInstance(ObjectManager::class);
    $dataMapper = $objectManager->get(DataMapper::class);
    $tableName = $dataMapper->getDataMap($this->objectType)->getTableName();
    return $tableName;
}

This works great with TYPO3 v10.4! :)

add this in the top of your repo: use TYPO3\CMS\Core\Database\ConnectionPool; use TYPO3\CMS\Core\Database\Query\Restriction\FrontendRestrictionContainer; use TYPO3\CMS\Core\Utility\GeneralUtility; use TYPO3\CMS\Extbase\Object\ObjectManager; use TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException; use TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper; — D2k, Sep 15 '21 at 09:11
Also, can you more explain what did you change and why this will fix the issue ? — Elikill58, Sep 15 '21 at 09:13
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-ask). — Community, Sep 15 '21 at 11:03

TYPO3 8.7 Query Sorting with Flexform uid´s

3 Answers3