Cannot "search by" nor "display custom field in the result list" for Models

[Du-St]

Hi,

I am using GLPI 9.4.5 (Fedora 30 RPM) and Fields plugin 1.10.2.

I have created custom block named "tier" with one field named "ZS tier" of the type Dropdown and associated the block with some Models (i.e. Computer, Monitor, ...).

Now it is not possible search in assets by Model's ZS tier value. Additionally, when I customized the view on the result list and added the ZS tier field, no results are shown.

I have already regenereated the container files.

I did some more investigation. I found out, that the generated SQL statement is invalid. In my case, it contains reference to the table "glpi_plugin_fields_computertiers" instead of "glpi_plugin_fields_computermodeltiers". The same is for Monitor models.

I would guess the same issues are probably reported in the #339 and #345, but for GLPI 9.4.4.

This is the SQL statement generated for the search of the Computers by the ZS tier field:

SELECT DISTINCT `glpi_computers`.`id` AS id, 'Administrator' AS currentuser,
                        `glpi_computers`.`entities_id`, `glpi_computers`.`is_recursive`,  `glpi_computers`.`name` AS `ITEM_Computer_1`,
                        `glpi_computers`.`id` AS `ITEM_Computer_1_id`,
                        `glpi_computers`.`otherserial` AS `ITEM_Computer_6`,   `glpi_users`.`name` AS `ITEM_Computer_70`,
                        `glpi_users`.`realname` AS `ITEM_Computer_70_realname`,
                        `glpi_users`.`id`  AS `ITEM_Computer_70_id`,
                        `glpi_users`.`firstname` AS `ITEM_Computer_70_firstname`,
                        `glpi_groups`.`completename` AS `ITEM_Computer_71`,   GROUP_CONCAT(DISTINCT CONCAT(`glpi_contracts_75a921d1c48c76553dc0a9e69050f745`.`name`, '$#$' ,
                                                        `glpi_contracts_75a921d1c48c76553dc0a9e69050f745`.`id`) ORDER BY `glpi_contracts_75a921d1c48c76553dc0a9e69050f745`.`id`
                                        SEPARATOR '$$##$$') AS `ITEM_Computer_29`,
                           
                           `glpi_locations`.`completename` AS `ITEM_Computer_3`,  `glpi_manufacturers`.`name` AS `ITEM_Computer_23`,  `glpi_computers`.`serial` AS `ITEM_Computer_5`,  `glpi_computertypes`.`name` AS `ITEM_Computer_4`,  `glpi_computermodels`.`name` AS `ITEM_Computer_40`,  `glpi_operatingsystems_9719987b154aaf3b42c3db32aef59090`.`name` AS `ITEM_Computer_45`,  `glpi_states`.`completename` AS `ITEM_Computer_31`,  `glpi_computers`.`date_mod` AS `ITEM_Computer_19`,   COUNT(DISTINCT `glpi_contracts_items`.`id`) AS `ITEM_Computer_139`,
                      GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`glpi_plugin_fields_zstierfielddropdowns_453ef002c336bcc435675331917fcb2f`.`completename`, '__NULL__'),
                                               '$#$',`glpi_plugin_fields_zstierfielddropdowns_453ef002c336bcc435675331917fcb2f`.`id`) ORDER BY `glpi_plugin_fields_zstierfielddropdowns_453ef002c336bcc435675331917fcb2f`.`id` SEPARATOR '$$##$$')
                              AS `ITEM_Computer_76665` FROM `glpi_computers`LEFT JOIN `glpi_users` 
                                          ON (`glpi_computers`.`users_id` = `glpi_users`.`id`
                                              )LEFT JOIN `glpi_groups` 
                                          ON (`glpi_computers`.`groups_id` = `glpi_groups`.`id`
                                              ) LEFT JOIN `glpi_contracts_items` 
                                          ON (`glpi_computers`.`id` = `glpi_contracts_items`.`items_id`
                                              AND `glpi_contracts_items`.`itemtype` = 'Computer'
                                              ) LEFT JOIN `glpi_contracts`  AS `glpi_contracts_75a921d1c48c76553dc0a9e69050f745`
                                          ON (`glpi_contracts_items`.`contracts_id` = `glpi_contracts_75a921d1c48c76553dc0a9e69050f745`.`id`
                                              )LEFT JOIN `glpi_locations` 
                                          ON (`glpi_computers`.`locations_id` = `glpi_locations`.`id`
                                              )LEFT JOIN `glpi_manufacturers` 
                                          ON (`glpi_computers`.`manufacturers_id` = `glpi_manufacturers`.`id`
                                              )LEFT JOIN `glpi_computertypes` 
                                          ON (`glpi_computers`.`computertypes_id` = `glpi_computertypes`.`id`
                                              )LEFT JOIN `glpi_computermodels` 
                                          ON (`glpi_computers`.`computermodels_id` = `glpi_computermodels`.`id`
                                              ) LEFT JOIN `glpi_items_operatingsystems` 
                                          ON (`glpi_computers`.`id` = `glpi_items_operatingsystems`.`items_id`
                                              AND `glpi_items_operatingsystems`.`itemtype` = 'Computer'
                                              ) LEFT JOIN `glpi_operatingsystems`  AS `glpi_operatingsystems_9719987b154aaf3b42c3db32aef59090`
                                          ON (`glpi_items_operatingsystems`.`operatingsystems_id` = `glpi_operatingsystems_9719987b154aaf3b42c3db32aef59090`.`id`
                                              )LEFT JOIN `glpi_states` 
                                          ON (`glpi_computers`.`states_id` = `glpi_states`.`id`
                                              ) LEFT JOIN `glpi_plugin_fields_computertiers` 
                                          ON (`glpi_computers`.`id` = `glpi_plugin_fields_computertiers`.`items_id`
                                              AND `glpi_plugin_fields_computertiers`.`itemtype` = 'Computer'
                                              ) LEFT JOIN `glpi_plugin_fields_zstierfielddropdowns`  AS `glpi_plugin_fields_zstierfielddropdowns_453ef002c336bcc435675331917fcb2f`
                                          ON (`glpi_plugin_fields_computertiers`.`plugin_fields_zstierfielddropdowns_id` = `glpi_plugin_fields_zstierfielddropdowns_453ef002c336bcc435675331917fcb2f`.`id`
                                              ) WHERE  `glpi_computers`.`is_deleted` = 0  AND `glpi_computers`.`is_template` = 0  AND (    (`glpi_plugin_fields_zstierfielddropdowns_453ef002c336bcc435675331917fcb2f`.`id` = '1') ) GROUP BY `glpi_computers`.`id` ORDER BY ITEM_Computer_1 ASC

[stonebuzz]

Hi @Du-St

thanks for feedback, can you test with develop branch ?

Best regards

[Du-St]

Hi @stonebuzz ,

I have just installed the new version 1.10.3 and regenerated the container files and the problem persists. Still the same wrong SQL statement is generated, still uses glpi_plugin_fields_computertiers instead of glpi_plugin_fields_computermodeltiers.

[stonebuzz]

Hi @Du-St

Can you show me fields configuration for computer ?

Best regards

[Du-St]

Hi @stonebuzz, these are sql statements from db dump:

INSERT INTO `glpi_plugin_fields_containers` VALUES (1,'tier','tier','[\"CartridgeItem\",\"ConsumableItem\",\"ComputerModel\",\"NetworkEquipmentModel\",\"PrinterModel\",\"MonitorModel\",\"PeripheralModel\",\"PhoneModel\",\"DeviceCaseModel\",\"DeviceControlModel\",\"DeviceDriveModel\",\"DeviceGenericModel\",\"DeviceGraphicCardModel\",\"DeviceHardDriveModel\",\"DeviceMemoryModel\",\"DeviceMotherBoardModel\",\"DeviceNetworkCardModel\",\"DevicePciModel\",\"DevicePowerSupplyModel\",\"DeviceProcessorModel\",\"DeviceSoundCardModel\",\"DeviceSensorModel\",\"RackModel\",\"EnclosureModel\",\"PDUModel\",\"VirtualMachineSystem\",\"PluginGenericobjectFacilitiemodel\"]','dom',NULL,0,0,1);

INSERT INTO `glpi_plugin_fields_fields` VALUES (1,'zstierfield','ZS tier','dropdown',1,1,'',1,0,0);

CREATE TABLE `glpi_plugin_fields_computermodeltiers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `items_id` int(11) NOT NULL,
  `itemtype` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'ComputerModel',
  `plugin_fields_containers_id` int(11) NOT NULL DEFAULT 1,
  `plugin_fields_zstierfielddropdowns_id` int(11) NOT NULL DEFAULT 0,
  `zstierfield` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `itemtype_item_container` (`itemtype`,`items_id`,`plugin_fields_containers_id`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

[stonebuzz]

Hi @Du-St i just want a screehshot about field configuration for "tier" container

Best regards

[Du-St]

@stonebuzz see the attachment please.

[stonebuzz]

Hi @Du-St

thanks for feedback, can you test with develop branch ?

PR #350 is merged into develop and fix your problem

Best regards