Bug #7838
closedsqlValidate does not work with inner query
0%
Description
QFQ-Version: 19.01.1
I do have a beforeSave element with the following validation setup:
sqlValidate={{!SELECT id FROM `phd_milestone_subrecords` WHERE milestone_id='{{id:R0}}' LIMIT {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }} }}
expectRecords = {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }}
messageFail = Wrong number of entries.
This does not raise the error even if the needed number of subrecords are missing. When replacing the inner query with 1 everything works.
Other places where I use inner queries seem to work correctly.
The problem can be avoided with:
sqlValidate={{!SELECT id FROM `phd_milestone_subrecords` WHERE milestone_id='{{id:R0}}' }}
expectRecords = {{SELECT GROUP_CONCAT(seq) FROM (SELECT seq FROM seq_0_to_30) AS positions WHERE seq >= (SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}') }}
messageFail = Wrong number of entries.
I have not investigated this much further, but am happy to create a Minimal Not-Working Example if needed :-)
Related issues
Updated by Carsten Rose about 5 years ago
- Status changed from New to Feedback
- Assignee set to Carsten Rose
Hallo Nicola
ich weiss nicht ob es das Problem ist , aber
- wenn die Inner Query keinen Record selektiert steht nur "... LIMIT"
- wenn die Inner Query mehrere Records selektiert (ist vermutlich nicht der Fall) waere fuer 'subrecords'=1|23|66 das Ergebnis '12366'
Lass dir mal das Ergebnis in anzeigen mit:
messageFail=Wrong number of entries. {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }}
Steht dort in allen Konstellationen die gewuenschte Zahl?
CU
Carsten
Updated by Nicola Chiapolini about 5 years ago
Mit
sqlValidate={{!SELECT id FROM `phd_milestone_subrecords` WHERE milestone_id='{{id:R0}}' LIMIT 1 }}
expectRecords = 1
messageFail = Wrong number of entries. {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }}
und keinem Record bekomme ich die Fehlermeldung
Wrong number of entries. >>1<<
Ebenso für
sqlValidate={{!SELECT id FROM `phd_milestone_subrecords` WHERE milestone_id='{{id:R0}}' LIMIT 1 }}
expectRecords = {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }}
messageFail = Wrong number of entries. >>{{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }}<<
Das Problem scheint also wirklich beim Umgang mit dem Inner-Query.
Updated by Carsten Rose over 4 years ago
- Assignee changed from Carsten Rose to Nicola Chiapolini
Kannst Du mal laufen lassen:
sqlValidate={{!SELECT id, unknown.column FROM `phd_milestone_subrecords` WHERE milestone_id='{{id:R0}}' LIMIT {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }} }}
Und die Fehlermeldung posten - dort steht hoffentlich was QFQ wie ersetzt hat.
Danke
Updated by Nicola Chiapolini over 4 years ago
Habe das getestet, das gibt keine Fehlermeldung -> sqlValidate wird gar nicht ausgeführt.
Wenn ich das LIMIT-Subquery durch 1 ersetze, bekomme ich den erwarteten Fehler. Wenn ich das LIMIT-Subquery durch {{ SELECT 1 }} ersetzen, wird das Query ebenfalls nicht ausgeführt.
Updated by Carsten Rose over 4 years ago
- Related to Support #9074: fillStoreVar: seems not to parse nested QFQ query added
Updated by Carsten Rose over 4 years ago
- Status changed from Feedback to Closed
Fixed in #9074 / 19.9.1