#94349Extbase n:m setOrderings() does not sort correct
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