Bug #86151
openDatabaseQueryProcessor find_in_set in orderBy Clause fails
0%
Description
Code from current 7.6 project - verified to work properly
# Add content element teaser tt_content { menu =< lib.fluidContent menu { templateName = Teaser.html dataProcessing { 10 = TYPO3\CMS\Frontend\DataProcessing\DatabaseQueryProcessor 10 { if.isTrue.field = pages table = pages uidInList.field = pages pidInList = 0 as = pageUids orderBy { dataWrap = find_in_set(uid,'|') field = pages } dataProcessing { 10 = TYPO3\CMS\Frontend\DataProcessing\FilesProcessor 10 { references.fieldName = media as = files } } } 20 < .10 20 { if.isTrue.field = selected_categories fieldName = selected_categories as = categoryUids } } stdWrap { # Setup the edit icon for content element "menu" editIcons = tt_content: header [header_layout], menu_type, pages editIcons { iconTitle.data = LLL:EXT:fluid_styled_content/Resources/Private/Language/FrontendEditing.xlf:editIcon.menu } } } }
The code utilizes find_in_set to ensure, that the order of the values in the select field is honored for the menu entries.
The problem lays in
\TYPO3\CMS\Frontend\DataProcessing\DatabaseQueryProcessor::process -> L 93 \TYPO3\CMS\Frontend\ContentObject\ContentObjectRenderer::getRecords -> L 7075 \TYPO3\CMS\Frontend\ContentObject\ContentObjectRenderer::exec_getQuery -> L 7056 \TYPO3\CMS\Frontend\ContentObject\ContentObjectRenderer::getQuery -> L 7224 \TYPO3\CMS\Core\Database\Query\QueryBuilder::addOrderBy --> L 811 $this->concreteQueryBuilder->addOrderBy($this->connection->quoteIdentifier($fieldName), $order); ^-- add escaping sequence, which breaks the query for that special case
I would expect the find_in_set function to work properly.
We have 3 working workarounds, which are both not optimal:
- remove ordering or order via title
- use a second php based Dataprocessor to get the ordering right
- use native queries and bypass doctrine querybuilder
I consider that as a bug, as it breaks functionality which was there for a very long time.
On the other hand escaping the values in the querybuilder might avoid certain security vectors.
I'm excited to get your feedback.
Updated by Kay Strobach over 5 years ago
btw. find_in_set is mysql specific, so the doctrine behaviour might be right. Maybe a more generic approach to that problem might solve it in the end
Updated by Susanne Moog about 4 years ago
- Category set to Database API (Doctrine DBAL)
Updated by Henrik Ziegenhain about 4 years ago
Hi, this issue is reproducible at least for TYPO3 v8 and v9 - did not test v10 yet.
In my eyes "find_in_set" is the only way to get the ordering out of a select field when using the DatabaseQueryProcessor
Updated by Matthias Kappenberg almost 4 years ago
This should work in 8,9 and 10
dataProcessing.10 = TYPO3\CMS\Frontend\DataProcessing\DatabaseQueryProcessor dataProcessing.10 { // next line does not workt A: will be escaped, B: unknown colunm error.. // orderBy.dataWrap = FIND_IN_SET(uid,'{field:pages}') // what should do the trick selectFields.dataWrap = *,FIND_IN_SET(uid,'{field:pages}') AS foobar_sort orderBy = foobar_sort .....
Updated by Niels Tiedt over 2 years ago
Matthias Kappenberg wrote in #note-4:
This should work in 8,9 and 10
[...]
Can confirm that this problem also exist in TYPO3 v10 and your solution work!
Updated by Michael Oehlhof about 1 year ago
Niels Tiedt wrote in #note-5:
Matthias Kappenberg wrote in #note-4:
This should work in 8,9 and 10
[...]Can confirm that this problem also exist in TYPO3 v10 and your solution work!
When using the QueryBuilder in an Extension the solution won't work because adding "FIND_IN_SET(...) AS ..." to the select field list escaped the whole expression.
Updated by Jasmina Ließmann 11 months ago
The solution described in #86151#note-4 also works in TYPO3 v11.
However, Workspace cannot handle this - using the preview in Workspace returns an error:
(1/3) Doctrine\DBAL\Exception\InvalidFieldNameException An exception occurred while executing 'SELECT `uid`, `pid`, ... , `foobar_sort` FROM `tt_content` WHERE (`t3ver_wsid` = ?) AND (((`uid` = ?) AND (`t3ver_state` = ?)) OR (`t3ver_oid` = ?)) AND (`tt_content`.`deleted` = 0) LIMIT 1' with params [...]: Unknown column 'foobar_sort' in 'field list'
Updated by Matthias Kappenberg 6 months ago
Workspace seems to me like an orphan child, ...
Sorry that my solution does not provide a workaround.
Is it possible that you share more information about your Workspace Setup?
And when the DB error pops up?
Updated by Andreas Kokott 6 months ago
Jasmina Ließmann wrote in #note-7:
The solution described in #86151#note-4 also works in TYPO3 v11.
However, Workspace cannot handle this - using the preview in Workspace returns an error:[...]
A possible workaround for the issue in workspaces would be to use an existing column for the solution Matthias described.
Using a sorting field instead of foobar_sort worked without side effects for me.
Updated by Jasmina Ließmann 4 months ago
Andreas Kokott wrote in #note-9:
A possible workaround for the issue in workspaces would be to use an existing column for the solution Matthias described.
Using a sorting field instead of foobar_sort worked without side effects for me.
I can confirm Andreas' suggested solution. Using an existing sort field solved the problem for me.