pluginsGLPI
getDropdownValue ajax function thrown sql ambigous error with oject GLPI field
Hi everyone,
The "GLPI object" of the formcreator plugin field throw an sql error when a self-service user try to retrieve a ticket from. The error only appear when a self-service user try to search a ticket. More elevated users role can acces list of tickets whitout any problem. This field is no longer usable in this case but we need information (title and id) of a ticket in the form the self-service user want to submit.
Thx for your help.
To Reproduce
- Create form with one field type "GLPI object" with formCreator plugin
- Setup field on "tickets" (other settings no longer change the issue)
- Save form
- Try to fill it with self-service user, the list stay emtpy, impossible to use autocomplete search andlog/sql-error.log show error sql "id... is ambigous" thrown by DBmysql::doQuery()
- Try to fill it with other privileges and list is ok.
Expected behavior
Just want to add list of tickets and ids which i can pick when using autocompletion field GLPI object
GLPI / Plugins :
- GLPI version 10.0.2
- Form Creator Version: 2.13.8 State: Enabled
- FusionInventory Version: 10.0.6+1.1 State: Enabled
- Imports fabricants Version: 3.0.5 State: Enabled
- Oauth IMAP Version: 1.4.3 State: Enabled
Sorry, I forgot this essential part...
[2024-02-07 08:02:04] glpisqllog.ERROR: DBmysql::doQuery() in /glpi/src/DBmysql.php line 403
*** MySQL query error:
SQL: SELECT DISTINCT `glpi_tickets`.* FROM `glpi_tickets` LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`
ON (`glpi_tickets`.`id` = `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`tickets_id`
AND `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.`type` = '1' ) LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd`
ON (`glpi_tickets`.`id` = `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd`.`tickets_id`
AND `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd`.`type` = '3' ) LEFT JOIN `glpi_groups_tickets` AS `glpi_groups_tickets_9c6b5b644d74a9e8013c7c2ea051dffd`
ON (`glpi_tickets`.`id` = `glpi_groups_tickets_9c6b5b644d74a9e8013c7c2ea051dffd`.`tickets_id`
AND `glpi_groups_tickets_9c6b5b644d74a9e8013c7c2ea051dffd`.`type` = '3' ) LEFT JOIN `glpi_tickets_users` AS `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`
ON (`glpi_tickets`.`id` = `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`tickets_id`
AND `glpi_tickets_users_c6f7f58b9365babf7388e555e526efd5`.`type` = '2' ) LEFT JOIN `glpi_groups_tickets` AS `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`
ON (`glpi_tickets`.`id` = `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`.`tickets_id`
AND `glpi_groups_tickets_c6f7f58b9365babf7388e555e526efd5`.`type` = '2' ) WHERE `glpi_tickets`.`is_deleted` = '0' AND (`users_id_recipient` = '11' OR (`id` IN (SELECT `tickets_id` FROM `glpi_tickets_users` WHERE `users_id` = '11' AND `type` IN ('1', '3')))) AND (`glpi_tickets`.`entities_id` IN ('0')) AND ( `glpi_tickets_users_019878060c6d5f06cbe3c4d7c31dec24`.users_id = '11'
OR `glpi_tickets_users_9c6b5b644d74a9e8013c7c2ea051dffd`.users_id = '11'
OR `glpi_tickets`.`users_id_recipient` = '11') ORDER BY `glpi_tickets`.`name` LIMIT 100
Error: Column 'id' in IN/ALL/ANY subquery is ambiguous
Backtrace :
src/DBmysqlIterator.php:112 DBmysql->doQuery()
src/DBmysql.php:1109 DBmysqlIterator->execute()
src/Dropdown.php:3346 DBmysql->request()
ajax/getDropdownValue.php:50 Dropdown::getDropdownValue()
public/index.php:82 require()
I see where the bug occurs in the SQL query; I think i'll have time in a few days to search the fix.
I see where the bug occurs in the SQL query; I think i'll have time in a few days to search the fix.
Hello there ! Did you find any solution for this issue ? Can we hope for a patch soon (if it's possible for sure)?
Thx
Hi
Could you try this patch ?
Be careful, I'm not sure I choosed the right table, then check the results to see if there is no unwanted item in the list.
diff --git a/inc/field/dropdownfield.class.php b/inc/field/dropdownfield.class.php
index 725de4de2..23c0e190f 100644
--- a/inc/field/dropdownfield.class.php
+++ b/inc/field/dropdownfield.class.php
@@ -245,7 +245,7 @@ class DropdownField extends PluginFormcreatorAbstractField
],
]);
$tickets_filter[] = [
- 'id' => $requestersObserversQuery,
+ Ticket::getTableField('id') => $requestersObserversQuery,
];
}
Hi
Could you try this patch ?
Be careful, I'm not sure I choosed the right table, then check the results to see if there is no unwanted item in the list.
diff --git a/inc/field/dropdownfield.class.php b/inc/field/dropdownfield.class.php index 725de4de2..23c0e190f 100644 --- a/inc/field/dropdownfield.class.php +++ b/inc/field/dropdownfield.class.php @@ -245,7 +245,7 @@ class DropdownField extends PluginFormcreatorAbstractField ], ]); $tickets_filter[] = [ - 'id' => $requestersObserversQuery, + Ticket::getTableField('id') => $requestersObserversQuery, ]; }
Hi, Your patch works perfectly ! It's a go for a release ;)
Thanks
Hi
No, I won't release any new version of Formcreator : the plugin reached its end of life because forms will be avalable in GLPI 11, and this makes this plugin obsolete.
If you use 2.13.8 and have some patches, keep them just in case. you need to rebuild your instance.
Fix pushed in the branch
https://github.com/pluginsGLPI/formcreator/commit/3c0630554e13dff1f604faa9de5f03c04eee9efa
Thank you for your feedback
No, I won't release any new version of Formcreator : the plugin reached its end of life because forms will be avalable in GLPI 11, and this makes this plugin obsolete.
If you use 2.13.8 and have some patches, keep them just in case. you need to rebuild your instance.
Ok. I didn't knew that forms will be integrated to GLPI 11.
Thx for your replies and help.