Bug #91201
closed
DB check module: DeletedRestriction are always added to the QueryBuilder
0%
Description
Problem/Description¶
Using the Full search feature of the QueryView allows the backend administrator to add the deleted field to the Select fields form field. In the built query the DeletedRestriction::class is always added as restriction, therefore it's actually useless to add a where clause on the deleted field in the form.
Affected TYPO3 versions at the time of issue-creation¶
- 10.4.1-dev
- 9.5
I doesn't tested older versions
Example of resulting DB query¶
SELECT
`uid`,
`type`,
`sys_language_uid`,
`pid`,
`deleted`,
`deleted`
FROM
`tx_theme_feature_teaser`
WHERE
(
tx_theme_feature_teaser.sys_language_uid = '1' AND tx_theme_feature_teaser.deleted = '1'
) AND(
`tx_theme_feature_teaser`.`deleted` = 0
)
LIMIT 100
What you can see?¶
deletedfield is added twice for the select (the first is from the manually added, the second from the code)- beside that the
deletedfield has a check for quoted1(this works actually at least in MySQL (I had a issue for that #87710)), - the code adds always an additional check "unquoted" on deleted if it is
0
ToDo(s)¶
It seems to me that this is a mistake. As an administrator, you have to be able to search for deleted records. If this should never be the case, the column must not be selectable by the TYPO3 administrator.
I can imagine that these restrictions (->add(GeneralUtility::makeInstance(DeletedRestriction::class))) should be added as needed depending on the built query by the administrator.
Updated by
Updated by Riccardo De Contardi over 4 years ago
As far as I have understood, this behavior is the standard one, but I miss this option, too.
Please note that there is a checkbox "Show even deleted entries (with undelete buttons)" but it does not seem to work...
Updated by Oliver Bartsch about 3 years ago
Hi Riccardo. I just came across this issue. What exactly do you mean by "but it does not seem to work...". Could you please check the latest versions if your issue still exists?
Updated by Riccardo De Contardi over 1 year ago
Sorry for having neglected this issue for so long! I performed the following tests with TYPO3 9.5.31, 10.4.37, 11.5.28, 12.4.2, 13.0.0-dev
DB Check > Full Search > Advanced Query
1) Build Up a query with the following parameters:
Select Table: Page Content [tt_content]
Select fields: uid, header, ctype
Make Query:
Type [CType] equals Header
Note: it looks like it is not possible to remove this kind of "restriction": there must be at least one of them
Group by: empty
Order by: empty
Limit: 100
Result¶
The SQL Query shown (and performed) for ALL the TYPO3 versions tested is:
SELECT `uid`, `header`, `CType`, `pid`, `deleted` FROM `tt_content` WHERE (tt_content.CType = 'header') AND (`tt_content`.`deleted` = 0) LIMIT 100
2) (that's the part regarding this issue):
Change the "Make query" part to:
[Field:Deleted] [deleted] is true
Note: the interface with the "Is true" with a caret that suggest a dropdown (actually absent) and the checkbox that swaps the value to "is false" is truly counterintuitive!!
Result¶
The SQL Query shown (and performed) for ALL the TYPO3 versions tested is:
SELECT `uid`, `header`, `CType`, `pid`, `deleted` FROM `tt_content` WHERE (tt_content.deleted = '1') AND (`tt_content`.`deleted` = 0) LIMIT 100
And the query is self-contradictory!
It should be possible to remove the AND (`tt_content`.`deleted` = 0) and that's the purpose of the checkbox "Show even deleted entries" (with undelete buttons)" at the beginning of the page.
If I check it the page reload itself; performing the initial query (point 1)) the following behavior occur:
TYPO3 9.5.31
Nothing happens. The SQL Query shown (and performed) is the same as before
SELECT `uid`, `header`, `CType`, `pid`, `deleted` FROM `tt_content` WHERE (tt_content.CType = 'header') AND (`tt_content`.`deleted` = 0) LIMIT 100
And this is why I wrote " but it does not seem to work." :)
TYPO3 10.4.37, 11.5.28, 12.4.2 13.0.0-dev:
The SQL Query shown (and performed) becomes:
SELECT `uid`, `header`, `CType`, `pid`, `deleted` FROM `tt_content` WHERE tt_content.CType = 'header' LIMIT 100
so now the setting is actually working.
If I combine BOTH "Show even deleted entries" (with undelete buttons)" AND [Field:Deleted] [deleted] is true The SQL Query shown (and performed) becomes:
SELECT `uid`, `header`, `CType`, `pid`, `deleted` FROM `tt_content` WHERE tt_content.deleted = '1' LIMIT 100
Conclusion¶
If the issue is about _ searching only deleted records _ , IMHO it is still present on 9.5.31 but solved on 10.4.37 and above.
if it is about the fact that you can build a self-contradictory query like ... WHERE (tt_content.deleted = '1') AND (`tt_content`.`deleted` = 0) ...
it is still present. It would be nice to exclude the "delete" field from the selectable fields when "Show even deleted entries" (with undelete buttons)" is not checked. I don't know if it is possible (or if it is worth the effort: maybe there are other combinations that makes a nonsense query and going after every possible combination is too difficult - I hope I explained myself.)
Said that, the interface still needs a lot of refinement!
Updated by Riccardo De Contardi over 1 year ago
- Status changed from New to Closed
I close this issue as now it is solved for TYPO3 version 10+
The UX of DB Check module will be tackled using fresh issues
Thank you.