Bug #86151
open
DatabaseQueryProcessor 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 over 3 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 10 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 5 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 5 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 3 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.