Issue Bug New Orphaned

#94349Extbase n:m setOrderings() does not sort correct

Heinz Schilling 06/15/21, 11:28 updated 04/07/22, 05:29

Description

I have courses and they have several start types and start dates.

starttype = 1 // date
starttype = 2 // on_request
starttype = 3 // continuously

So I sort the course by starttype and then by startdatetime. Now it results in an order which only use the first entry in starts and not the earliest. But if I use the query on database the ordering is as expected. It looks like there is some ‘magic’ between the query and the database. Is there a solution for my problem?

In my controller I have set the sorting as I found it in many examples

/**
 * Find all courses and sort them by start type and start date
 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
 */
public function findAllSorted() {
    $query = $this->createQuery();
    $query->setOrderings([
        'starts.starttype' => QueryInterface::ORDER_ASCENDING,
        'starts.startdatetime' => QueryInterface::ORDER_ASCENDING
    ]);
    $query->matching(
        $query->logicalOr([
            $query->greaterThan('starts.starttype',1),
            $query->greaterThan('starts.startdatetime', time())
        ]));

    // Debug query
    //$queryParser = $this->objectManager->get(\TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::class);
    //\TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($queryParser->convertQueryToDoctrineQueryBuilder($query)->getSQL());
    //\TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($queryParser->convertQueryToDoctrineQueryBuilder($query)->getParameters());

    return $query->execute();
}

This results in following query

SELECT `tx_vendorcontinuingeducation_domain_model_course`.*
FROM `tx_vendorcontinuingeducation_domain_model_course` `tx_vendorcontinuingeducation_domain_model_course`
LEFT JOIN `tx_vendorcontinuingeducation_domain_model_start` `tx_vendorcontinuingeducation_domain_model_start` ON `tx_vendorcontinuingeducation_domain_model_course`.`uid` = `tx_vendorcontinuingeducation_domain_model_start`.`parent`
WHERE ((`tx_vendorcontinuingeducation_domain_model_start`.`starttype` > :dcValue1)
       OR (`tx_vendorcontinuingeducation_domain_model_start`.`startdatetime` > :dcValue2))
  AND (`tx_vendorcontinuingeducation_domain_model_course`.`sys_language_uid` IN (0,
                                                                                 -1))
  AND (`tx_vendorcontinuingeducation_domain_model_course`.`t3ver_oid` = 0)
  AND ((`tx_vendorcontinuingeducation_domain_model_course`.`deleted` = 0)
       AND (`tx_vendorcontinuingeducation_domain_model_course`.`t3ver_state` <= 0)
       AND (`tx_vendorcontinuingeducation_domain_model_course`.`t3ver_wsid` = 0)
       AND (`tx_vendorcontinuingeducation_domain_model_course`.`t3ver_oid` = 0)
       AND (`tx_vendorcontinuingeducation_domain_model_course`.`hidden` = 0)
       AND (`tx_vendorcontinuingeducation_domain_model_course`.`starttime` <= 1619622660)
       AND ((`tx_vendorcontinuingeducation_domain_model_course`.`endtime` = 0)
            OR (`tx_vendorcontinuingeducation_domain_model_course`.`endtime` > 1619622660))
       AND (((`tx_vendorcontinuingeducation_domain_model_course`.`fe_group` = '')
             OR (`tx_vendorcontinuingeducation_domain_model_course`.`fe_group` IS NULL)
             OR (`tx_vendorcontinuingeducation_domain_model_course`.`fe_group` = '0')
             OR (FIND_IN_SET('0', `tx_vendorcontinuingeducation_domain_model_course`.`fe_group`))
             OR (FIND_IN_SET('-1', `tx_vendorcontinuingeducation_domain_model_course`.`fe_group`)))))
  AND (((`tx_vendorcontinuingeducation_domain_model_start`.`deleted` = 0)
        AND (`tx_vendorcontinuingeducation_domain_model_start`.`t3ver_state` <= 0)
        AND (`tx_vendorcontinuingeducation_domain_model_start`.`t3ver_wsid` = 0)
        AND (`tx_vendorcontinuingeducation_domain_model_start`.`t3ver_oid` = 0)
        AND (`tx_vendorcontinuingeducation_domain_model_start`.`hidden` = 0)
        AND (`tx_vendorcontinuingeducation_domain_model_start`.`starttime` <= 1619622660)
        AND ((`tx_vendorcontinuingeducation_domain_model_start`.`endtime` = 0)
             OR (`tx_vendorcontinuingeducation_domain_model_start`.`endtime` > 1619622660))
        AND (((`tx_vendorcontinuingeducation_domain_model_start`.`fe_group` = '')
              OR (`tx_vendorcontinuingeducation_domain_model_start`.`fe_group` IS NULL)
              OR (`tx_vendorcontinuingeducation_domain_model_start`.`fe_group` = '0')
              OR (FIND_IN_SET('0', `tx_vendorcontinuingeducation_domain_model_start`.`fe_group`))
              OR (FIND_IN_SET('-1', `tx_vendorcontinuingeducation_domain_model_start`.`fe_group`)))))
       OR (`tx_vendorcontinuingeducation_domain_model_start`.`uid` IS NULL))
ORDER BY `tx_vendorcontinuingeducation_domain_model_start`.`starttype` ASC,
         `tx_vendorcontinuingeducation_domain_model_start`.`startdatetime` ASC

and the values

array(2 items)
   dcValue1 => 1 (integer)
   dcValue2 => 1619622708 (integer)

Now I replace the dcValues and copy them to my phpMyAdmin

SELECT `tx_vendorcontinuingeducation_domain_model_course`.*
FROM `tx_vendorcontinuingeducation_domain_model_course` `tx_vendorcontinuingeducation_domain_model_course`
LEFT JOIN `tx_vendorcontinuingeducation_domain_model_start` `tx_vendorcontinuingeducation_domain_model_start` ON `tx_vendorcontinuingeducation_domain_model_course`.`uid` = `tx_vendorcontinuingeducation_domain_model_start`.`parent`
WHERE ((`tx_vendorcontinuingeducation_domain_model_start`.`starttype` > 1)
       OR (`tx_vendorcontinuingeducation_domain_model_start`.`startdatetime` > 1619619086))
  AND (`tx_vendorcontinuingeducation_domain_model_course`.`sys_language_uid` IN (0,
                                                                                 -1))
  AND (`tx_vendorcontinuingeducation_domain_model_course`.`t3ver_oid` = 0)
  AND ((`tx_vendorcontinuingeducation_domain_model_course`.`deleted` = 0)
       AND (`tx_vendorcontinuingeducation_domain_model_course`.`t3ver_state` <= 0)
       AND (`tx_vendorcontinuingeducation_domain_model_course`.`t3ver_wsid` = 0)
       AND (`tx_vendorcontinuingeducation_domain_model_course`.`t3ver_oid` = 0)
       AND (`tx_vendorcontinuingeducation_domain_model_course`.`hidden` = 0)
       AND (`tx_vendorcontinuingeducation_domain_model_course`.`starttime` <= 1619619060)
       AND ((`tx_vendorcontinuingeducation_domain_model_course`.`endtime` = 0)
            OR (`tx_vendorcontinuingeducation_domain_model_course`.`endtime` > 1619619060))
       AND (((`tx_vendorcontinuingeducation_domain_model_course`.`fe_group` = '')
             OR (`tx_vendorcontinuingeducation_domain_model_course`.`fe_group` IS NULL)
             OR (`tx_vendorcontinuingeducation_domain_model_course`.`fe_group` = '0')
             OR (FIND_IN_SET('0', `tx_vendorcontinuingeducation_domain_model_course`.`fe_group`))
             OR (FIND_IN_SET('-1', `tx_vendorcontinuingeducation_domain_model_course`.`fe_group`)))))
  AND (((`tx_vendorcontinuingeducation_domain_model_start`.`deleted` = 0)
        AND (`tx_vendorcontinuingeducation_domain_model_start`.`t3ver_state` <= 0)
        AND (`tx_vendorcontinuingeducation_domain_model_start`.`t3ver_wsid` = 0)
        AND (`tx_vendorcontinuingeducation_domain_model_start`.`t3ver_oid` = 0)
        AND (`tx_vendorcontinuingeducation_domain_model_start`.`hidden` = 0)
        AND (`tx_vendorcontinuingeducation_domain_model_start`.`starttime` <= 1619619060)
        AND ((`tx_vendorcontinuingeducation_domain_model_start`.`endtime` = 0)
             OR (`tx_vendorcontinuingeducation_domain_model_start`.`endtime` > 1619619060))
        AND (((`tx_vendorcontinuingeducation_domain_model_start`.`fe_group` = '')
              OR (`tx_vendorcontinuingeducation_domain_model_start`.`fe_group` IS NULL)
              OR (`tx_vendorcontinuingeducation_domain_model_start`.`fe_group` = '0')
              OR (FIND_IN_SET('0', `tx_vendorcontinuingeducation_domain_model_start`.`fe_group`))
              OR (FIND_IN_SET('-1', `tx_vendorcontinuingeducation_domain_model_start`.`fe_group`)))))
       OR (`tx_vendorcontinuingeducation_domain_model_start`.`uid` IS NULL))
ORDER BY `tx_vendorcontinuingeducation_domain_model_start`.`starttype` ASC,
         `tx_vendorcontinuingeducation_domain_model_start`.`startdatetime` ASC

The sorting in phpMyAdmin is correct. Something changes between repository and database.

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

Fix this issue

Start the commit message of your patch with this. Add what the patch does, and the versions it goes to in "Releases:". How to create a patch

Commit message
[BUGFIX] Extbase n:m setOrderings() does not sort correct Resolves: #94349 Releases: main