Bug in one-to-many search relation

[rma]

Hi,

In Sugar 5.1.0

I have a realtion of one-to-many from a custom module to the Contacts module. I added a field in the search view of my custom module to look for contacts linked to this new module. But the problem is that the SQL query generated is wrong. See above:

SELECT  eds_ac.id  ,eds_ac_cstm.alloc_fam_c,eds_ac_cstm.alloc_logement_c,eds_ac_cstm.amal1_c,eds_ac_cstm.amal2_c,eds_ac_cstm.amal3_c,eds_ac_cstm.amal4_c,eds_ac_cstm.amal5_c,eds_ac_cstm.amal6_c,eds_ac_cstm.amal7_c,eds_ac_cstm.amal8_c,eds_ac_cstm.amaltot_c,eds_ac_cstm.amal_c,eds_ac_cstm.charges_c,eds_ac_cstm.charges_div_c,eds_ac_cstm.charges_div_just_c,eds_ac_cstm.cmpl_frais_div_c,eds_ac_cstm.datedemande_c,eds_ac_cstm.datereponse_c,eds_ac_cstm.diff_reven_ress_c,eds_ac_cstm.fortune_brute_c,eds_ac_cstm.impot_source_c,eds_ac_cstm.limite_revenu_c,eds_ac_cstm.loyer_c,eds_ac_cstm.loyer_pris_en_compte_c,eds_ac_cstm.montantac_c,eds_ac_cstm.nbpersonne_c,eds_ac_cstm.pension_recue_c,eds_ac_cstm.pension_versee_c,eds_ac_cstm.rentes_c,eds_ac_cstm.ressources_div_c,eds_ac_cstm.ress_div_just_c,eds_ac_cstm.revenu_fortune_c,eds_ac_cstm.reven_determinant_c,eds_ac_cstm.salaire_conj_c,eds_ac_cstm.salaire_de_c,eds_ac_cstm.salaire_net_c,eds_ac_cstm.statutdemande_c,eds_ac_cstm.subside1_c,eds_ac_cstm.subside2_c,eds_ac_cstm.subside3_c,eds_ac_cstm.subside4_c,eds_ac_cstm.subside5_c,eds_ac_cstm.subside6_c,eds_ac_cstm.subside7_c,eds_ac_cstm.subside8_c,eds_ac_cstm.subside_tot_c,eds_ac_cstm.total_depen_c,eds_ac_cstm.total_ressource_c, eds_ac.name  , CONCAT(IFNULL(jt0.first_name,''),' ',IFNULL(jt0.last_name,'')) contacts_eds_ac_name, jtl0.contacts_edaccontacts_ida contacts_edaccontacts_ida, eds_ac.assigned_user_id  FROM eds_ac   LEFT JOIN eds_ac_cstm ON eds_ac.id = eds_ac_cstm.id_c   LEFT JOIN  contacts_eds_ac_c jtl0 ON eds_ac.id=jtl0.contacts_eds_aceds_ac_idb  AND jtl0.deleted=0
 LEFT JOIN  contacts jt0 ON jt0.id=jtl0.contacts_edaccontacts_ida AND jt0.deleted=0
 AND jt0.deleted=0 where ((jt0.name like 'M. Dupont Pierre%')) AND eds_ac.deleted=0 LIMIT 0,21

You can see that at the end of the query, sugar look for : name like "Dupont Pierre" in the contacts table. But there is no name field. It should look into the fields first_name and last_name.

Posted a bug report N°25341 : http://www.sugarcrm.com/crm/?option=...1-48ea14dbb7ee

[mikesolomon]

Hi,

In Sugar 5.1.0

I have a realtion of one-to-many from a custom module to the Contacts module. I added a field in the search view of my custom module to look for contacts linked to this new module. But the problem is that the SQL query generated is wrong. See above:

SELECT  eds_ac.id  ,eds_ac_cstm.alloc_fam_c,eds_ac_cstm.alloc_logement_c,eds_ac_cstm.amal1_c,eds_ac_cstm.amal2_c,eds_ac_cstm.amal3_c,eds_ac_cstm.amal4_c,eds_ac_cstm.amal5_c,eds_ac_cstm.amal6_c,eds_ac_cstm.amal7_c,eds_ac_cstm.amal8_c,eds_ac_cstm.amaltot_c,eds_ac_cstm.amal_c,eds_ac_cstm.charges_c,eds_ac_cstm.charges_div_c,eds_ac_cstm.charges_div_just_c,eds_ac_cstm.cmpl_frais_div_c,eds_ac_cstm.datedemande_c,eds_ac_cstm.datereponse_c,eds_ac_cstm.diff_reven_ress_c,eds_ac_cstm.fortune_brute_c,eds_ac_cstm.impot_source_c,eds_ac_cstm.limite_revenu_c,eds_ac_cstm.loyer_c,eds_ac_cstm.loyer_pris_en_compte_c,eds_ac_cstm.montantac_c,eds_ac_cstm.nbpersonne_c,eds_ac_cstm.pension_recue_c,eds_ac_cstm.pension_versee_c,eds_ac_cstm.rentes_c,eds_ac_cstm.ressources_div_c,eds_ac_cstm.ress_div_just_c,eds_ac_cstm.revenu_fortune_c,eds_ac_cstm.reven_determinant_c,eds_ac_cstm.salaire_conj_c,eds_ac_cstm.salaire_de_c,eds_ac_cstm.salaire_net_c,eds_ac_cstm.statutdemande_c,eds_ac_cstm.subside1_c,eds_ac_cstm.subside2_c,eds_ac_cstm.subside3_c,eds_ac_cstm.subside4_c,eds_ac_cstm.subside5_c,eds_ac_cstm.subside6_c,eds_ac_cstm.subside7_c,eds_ac_cstm.subside8_c,eds_ac_cstm.subside_tot_c,eds_ac_cstm.total_depen_c,eds_ac_cstm.total_ressource_c, eds_ac.name  , CONCAT(IFNULL(jt0.first_name,''),' ',IFNULL(jt0.last_name,'')) contacts_eds_ac_name, jtl0.contacts_edaccontacts_ida contacts_edaccontacts_ida, eds_ac.assigned_user_id  FROM eds_ac   LEFT JOIN eds_ac_cstm ON eds_ac.id = eds_ac_cstm.id_c   LEFT JOIN  contacts_eds_ac_c jtl0 ON eds_ac.id=jtl0.contacts_eds_aceds_ac_idb  AND jtl0.deleted=0
 LEFT JOIN  contacts jt0 ON jt0.id=jtl0.contacts_edaccontacts_ida AND jt0.deleted=0
 AND jt0.deleted=0 where ((jt0.name like 'M. Dupont Pierre%')) AND eds_ac.deleted=0 LIMIT 0,21

You can see that at the end of the query, sugar look for : name like "Dupont Pierre" in the contacts table. But there is no name field. It should look into the fields first_name and last_name.

Posted a bug report N°25341 : http://www.sugarcrm.com/crm/?option=...1-48ea14dbb7ee

In your modules SearchFields.php put

'contact_name' => array( 'query_type'=>'default','db_field'=>array('contact s.first_name','contacts.last_name')),

Hopefully it will fix your problem

[rma]

It works.
Thank you

Rosanna

[rma]

Sorry, in fact it's not perfectly working.
The generated SQL query look like this:

(( contacts.first_name like 'M. Dupont Pierre%'
OR contacts.last_name like 'M. Dupont Pierre%' ))

So if we add only the first name or the last name, the contact will be found. But in my case, it's a select field that return the full name of the contact (Salutations + first name + last name). I would like to keep this.

Any idea how to cut the full name to create a correct sql query, that should look like :

(( contacts.first_name like 'Pierre%'
OR contacts.last_name like 'Dupont%' ))

Thanks a lot for your help.
Cheers.

Rosanna