TYPO3 Core

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.