Forum GLPI-Project

jerdlmrh

Member

Registered: 2009-01-31

Posts: 9

Pb recherche : les mêmes ordinateurs apparaissent plusieurs fois

Version GLPI: 0.72.1
Plate-forme: CentOS 5.3

Description du pb :

- faire une recherche sur le nom des ordinateurs, mais choisir "Tous" comme sélecteur de champ de recherche

- la requête SQL (très longue pour être postée ici) sort 8 fois le même enregistrement !

- la même recherche en sélectionnant le champ "Elements visualisés", par exemple, n'en sort qu'un !

Proposition (patch bestial ?) :

ajouter un DISTINCT au SELECT de la ligne 600 dans le fichier inc/search.function.php

MoYo

GLPI - Lead

From: Poitiers

Registered: 2004-09-13

Posts: 14,513

Website

Re: Pb recherche : les mêmes ordinateurs apparaissent plusieurs fois

quelle caractéristique est en 8 exemplaire sur votre enregistrement ?

---

MoYo - Julien Dombre - Association INDEPNET
Contribute to GLPI :    Support     Contribute     References     Freshmeat

jerdlmrh

Member

Registered: 2009-01-31

Posts: 9

Re: Pb recherche : les mêmes ordinateurs apparaissent plusieurs fois

La requête SQL générée dans le cas cité est la suivante :

SELECT glpi_computers.name AS ITEM_0, glpi_computers.ID AS ITEM_0_2, glpi_entities.completename AS ITEM_1, glpi_entities.ID AS ITEM_1_2, glpi_dropdown_state.name AS ITEM_2, glpi_dropdown_manufacturer.name AS ITEM_3, glpi_computers.serial AS ITEM_4, glpi_type_computers.name AS ITEM_5, glpi_dropdown_model.name AS ITEM_6, glpi_dropdown_os.name AS ITEM_7, glpi_dropdown_locations.completename AS ITEM_8, glpi_computers.date_mod AS ITEM_9, glpi_computers.contact AS ITEM_10, glpi_computers.ID AS ID
FROM glpi_computers
LEFT JOIN glpi_entities ON (glpi_computers.FK_entities = glpi_entities.ID)
LEFT JOIN glpi_dropdown_state ON (glpi_computers.state = glpi_dropdown_state.ID)
LEFT JOIN glpi_dropdown_manufacturer ON (glpi_computers.FK_glpi_enterprise = glpi_dropdown_manufacturer.ID)
LEFT JOIN glpi_type_computers ON (glpi_computers.type = glpi_type_computers.ID)
LEFT JOIN glpi_dropdown_model ON (glpi_computers.model = glpi_dropdown_model.ID)
LEFT JOIN glpi_dropdown_os ON (glpi_computers.os = glpi_dropdown_os.ID)
LEFT JOIN glpi_dropdown_locations ON (glpi_computers.location = glpi_dropdown_locations.ID)
LEFT JOIN glpi_dropdown_os_version ON (glpi_computers.os_version = glpi_dropdown_os_version.ID)
LEFT JOIN glpi_dropdown_os_sp ON (glpi_computers.os_sp = glpi_dropdown_os_sp.ID)
LEFT JOIN glpi_dropdown_auto_update ON (glpi_computers.auto_update = glpi_dropdown_auto_update.ID)
LEFT JOIN glpi_users AS glpi_users_FK_users ON (glpi_computers.FK_users = glpi_users_FK_users.ID)
LEFT JOIN glpi_groups ON (glpi_computers.FK_groups = glpi_groups.ID)
LEFT JOIN glpi_dropdown_network ON (glpi_computers.network = glpi_dropdown_network.ID)
LEFT JOIN glpi_dropdown_domain ON (glpi_computers.domain = glpi_dropdown_domain.ID)
LEFT JOIN glpi_users AS glpi_users_tech_num ON (glpi_computers.tech_num = glpi_users_tech_num.ID)
LEFT JOIN glpi_computer_device AS DEVICE_2 ON (glpi_computers.ID = DEVICE_2.FK_computers AND DEVICE_2.device_type='2')
LEFT JOIN glpi_device_processor ON (DEVICE_2.FK_device = glpi_device_processor.ID)
LEFT JOIN glpi_computer_device AS DEVICE_3 ON (glpi_computers.ID = DEVICE_3.FK_computers AND DEVICE_3.device_type='3')
LEFT JOIN glpi_device_ram ON (DEVICE_3.FK_device = glpi_device_ram.ID)
LEFT JOIN glpi_computer_device AS DEVICE_5 ON (glpi_computers.ID = DEVICE_5.FK_computers AND DEVICE_5.device_type='5')
LEFT JOIN glpi_device_iface ON (DEVICE_5.FK_device = glpi_device_iface.ID)
LEFT JOIN glpi_computer_device AS DEVICE_9 ON (glpi_computers.ID = DEVICE_9.FK_computers AND DEVICE_9.device_type='9')
LEFT JOIN glpi_device_sndcard ON (DEVICE_9.FK_device = glpi_device_sndcard.ID)
LEFT JOIN glpi_computer_device AS DEVICE_8 ON (glpi_computers.ID = DEVICE_8.FK_computers AND DEVICE_8.device_type='8')
LEFT JOIN glpi_device_gfxcard ON (DEVICE_8.FK_device = glpi_device_gfxcard.ID)
LEFT JOIN glpi_computer_device AS DEVICE_1 ON (glpi_computers.ID = DEVICE_1.FK_computers AND DEVICE_1.device_type='1')
LEFT JOIN glpi_device_moboard ON (DEVICE_1.FK_device = glpi_device_moboard.ID)
LEFT JOIN glpi_computer_device AS DEVICE_4 ON (glpi_computers.ID = DEVICE_4.FK_computers AND DEVICE_4.device_type='4')
LEFT JOIN glpi_device_hdd ON (DEVICE_4.FK_device = glpi_device_hdd.ID)
LEFT JOIN glpi_computer_device AS DEVICE_12 ON (glpi_computers.ID = DEVICE_12.FK_computers AND DEVICE_12.device_type='12')
LEFT JOIN glpi_device_power ON (DEVICE_12.FK_device = glpi_device_power.ID)
LEFT JOIN glpi_computerdisks ON (glpi_computers.ID = glpi_computerdisks.FK_computers)
LEFT JOIN glpi_dropdown_filesystems ON (glpi_computerdisks.FK_filesystems = glpi_dropdown_filesystems.ID)
LEFT JOIN glpi_networking_ports ON (glpi_computers.ID = glpi_networking_ports.on_device AND glpi_networking_ports.device_type='1')
LEFT JOIN glpi_dropdown_netpoint ON (glpi_networking_ports.netpoint = glpi_dropdown_netpoint.ID)
LEFT JOIN glpi_tracking ON (glpi_tracking.device_type='1' AND glpi_computers.ID = glpi_tracking.computer)
LEFT JOIN glpi_contract_device ON (glpi_computers.ID = glpi_contract_device.FK_device AND glpi_contract_device.device_type='1')
LEFT JOIN glpi_contracts ON (glpi_contract_device.FK_contract = glpi_contracts.ID)
LEFT JOIN glpi_infocoms ON (glpi_computers.ID = glpi_infocoms.FK_device AND glpi_infocoms.device_type='1')
LEFT JOIN glpi_dropdown_budget ON (glpi_infocoms.budget = glpi_dropdown_budget.ID)
LEFT JOIN glpi_enterprises AS glpi_enterprises_infocoms ON (glpi_infocoms.FK_enterprise = glpi_enterprises_infocoms.ID)
LEFT JOIN glpi_ocs_link ON (glpi_computers.ID = glpi_ocs_link.glpi_id)
LEFT JOIN glpi_registry ON (glpi_computers.ID = glpi_registry.computer_id)
WHERE glpi_computers.deleted='0' AND glpi_computers.is_template='0' AND ( ( (glpi_computers.name LIKE '%pcg02b%' ) OR (glpi_computers.ID LIKE '%pcg02b%' ) OR (glpi_dropdown_locations.completename LIKE '%pcg02b%' ) OR (glpi_type_computers.name LIKE '%pcg02b%' ) OR (glpi_dropdown_model.name LIKE '%pcg02b%' ) OR (glpi_dropdown_state.name LIKE '%pcg02b%' ) OR (glpi_dropdown_os.name LIKE '%pcg02b%' ) OR (glpi_dropdown_os_version.name LIKE '%pcg02b%' ) OR (glpi_dropdown_os_sp.name LIKE '%pcg02b%' ) OR (glpi_dropdown_auto_update.name LIKE '%pcg02b%' ) OR (glpi_computers.os_license_number LIKE '%pcg02b%' ) OR (glpi_computers.os_license_id LIKE '%pcg02b%' ) OR (glpi_computers.serial LIKE '%pcg02b%' ) OR (glpi_computers.otherserial LIKE '%pcg02b%' ) OR (glpi_computers.comments LIKE '%pcg02b%' ) OR (glpi_computers.notes LIKE '%pcg02b%' ) OR (glpi_computers.contact LIKE '%pcg02b%' ) OR (glpi_computers.contact_num LIKE '%pcg02b%' ) OR ( glpi_users_FK_users.name LIKE '%pcg02b%' OR glpi_users_FK_users.realname LIKE '%pcg02b%' OR glpi_users_FK_users.firstname LIKE '%pcg02b%' OR CONCAT(glpi_users_FK_users.realname,' ',glpi_users_FK_users.firstname) LIKE '%pcg02b%' ) OR (glpi_groups.name LIKE '%pcg02b%' ) OR ( glpi_computers.date_mod LIKE '%pcg02b%' ) OR (glpi_dropdown_network.name LIKE '%pcg02b%' ) OR (glpi_dropdown_domain.name LIKE '%pcg02b%' ) OR (glpi_dropdown_manufacturer.name LIKE '%pcg02b%' ) OR ( glpi_users_tech_num.name LIKE '%pcg02b%' OR glpi_users_tech_num.realname LIKE '%pcg02b%' OR glpi_users_tech_num.firstname LIKE '%pcg02b%' OR CONCAT(glpi_users_tech_num.realname,' ',glpi_users_tech_num.firstname) LIKE '%pcg02b%' ) OR (glpi_entities.completename LIKE '%pcg02b%' ) OR (glpi_device_iface.designation LIKE '%pcg02b%' ) OR (glpi_device_sndcard.designation LIKE '%pcg02b%' ) OR (glpi_device_gfxcard.designation LIKE '%pcg02b%' ) OR (glpi_device_moboard.designation LIKE '%pcg02b%' ) OR (glpi_computerdisks.name LIKE '%pcg02b%' ) OR (glpi_computerdisks.mountpoint LIKE '%pcg02b%' ) OR (glpi_computerdisks.device LIKE '%pcg02b%' ) OR (glpi_dropdown_filesystems.name LIKE '%pcg02b%' ) OR (glpi_networking_ports.ifaddr LIKE '%pcg02b%' ) OR ( DEVICE_5.specificity LIKE '%pcg02b%' OR glpi_networking_ports.ifmac LIKE '%pcg02b%' ) OR (glpi_networking_ports.netmask LIKE '%pcg02b%' ) OR (glpi_networking_ports.subnet LIKE '%pcg02b%' ) OR (glpi_networking_ports.gateway LIKE '%pcg02b%' ) OR (glpi_dropdown_netpoint.name LIKE '%pcg02b%' ) OR (glpi_contracts.name LIKE '%pcg02b%' ) OR (glpi_contracts.num LIKE '%pcg02b%' ) OR (glpi_contracts.duration LIKE '%pcg02b%' ) OR (glpi_contracts.periodicity LIKE '%pcg02b%' ) OR ( glpi_contracts.begin_date LIKE '%pcg02b%' ) OR (glpi_contracts.compta_num LIKE '%pcg02b%' ) OR ( ADDDATE(glpi_contracts.begin_date, INTERVAL glpi_contracts.duration MONTH) LIKE '%pcg02b%' ) OR (glpi_contracts.notice LIKE '%pcg02b%' ) OR (glpi_contracts.cost LIKE '%pcg02b%' ) OR (glpi_contracts.facturation LIKE '%pcg02b%' ) OR glpi_contracts.renewal=0 OR (glpi_infocoms.num_immo LIKE '%pcg02b%' ) OR (glpi_infocoms.num_commande LIKE '%pcg02b%' ) OR (glpi_infocoms.bon_livraison LIKE '%pcg02b%' ) OR (glpi_infocoms.facture LIKE '%pcg02b%' ) OR ( glpi_infocoms.buy_date LIKE '%pcg02b%' ) OR ( glpi_infocoms.use_date LIKE '%pcg02b%' ) OR (glpi_dropdown_budget.name LIKE '%pcg02b%' ) OR (glpi_infocoms.warranty_duration LIKE '%pcg02b%' ) OR (glpi_infocoms.warranty_info LIKE '%pcg02b%' ) OR ( ADDDATE(glpi_infocoms.buy_date, INTERVAL glpi_infocoms.warranty_duration MONTH) LIKE '%pcg02b%' ) OR (glpi_enterprises_infocoms.name LIKE '%pcg02b%' ) OR (glpi_infocoms.value LIKE '%pcg02b%' ) OR (glpi_infocoms.warranty_value LIKE '%pcg02b%' ) OR (glpi_infocoms.amort_time LIKE '%pcg02b%' ) OR (glpi_infocoms.amort_type LIKE '%pcg02b%' ) OR (glpi_infocoms.amort_coeff LIKE '%pcg02b%' ) OR (glpi_infocoms.alert LIKE '%pcg02b%' ) OR (glpi_infocoms.comments LIKE '%pcg02b%' ) OR ( glpi_ocs_link.last_update LIKE '%pcg02b%' ) OR ( glpi_ocs_link.last_ocs_update LIKE '%pcg02b%' ) OR (glpi_computers.ocs_import LIKE '%pcg02b%' ) OR (glpi_ocs_link.auto_update LIKE '%pcg02b%' ) OR (glpi_registry.registry_value LIKE '%pcg02b%' ) OR (glpi_registry.registry_ocs_name LIKE '%pcg02b%' ) ) )
ORDER BY ITEM_6 DESC

et elle retourne 8 ordinateurs (voir image ci-dessous). Alors qu'il n'y en a qu'un seul qui porte le nom PCG02B dans glpi_computers.... d'où le DISTINCT pour corriger vite fait (mal fait ?).

MoYo

GLPI - Lead

From: Poitiers

Registered: 2004-09-13

Posts: 14,513

Website

Re: Pb recherche : les mêmes ordinateurs apparaissent plusieurs fois

je reproduit.
je vais voir. mais je pense que la meilleure solution n'est pas le distinct

---

MoYo - Julien Dombre - Association INDEPNET
Contribute to GLPI :    Support     Contribute     References     Freshmeat

MoYo

GLPI - Lead

From: Poitiers

Registered: 2004-09-13

Posts: 14,513

Website

Re: Pb recherche : les mêmes ordinateurs apparaissent plusieurs fois

la correction ici :

https://forge.indepnet.net/repositories … i?rev=8765

sera inclus en 0.72.2

je ferme

---

MoYo - Julien Dombre - Association INDEPNET
Contribute to GLPI :    Support     Contribute     References     Freshmeat