Cross module search implementation

Hi all,

This post is more of a description of how I have implemented cross module search rather than a question. I though it would be good to put this out there for others who might benefit from it but also as a form of peer review to critique my approach (I’m no sugar/suite crm expert!).

I have spent a couple of days trawling through the web trying to figure out cross module searching. I found a couple of 'how to’s most notably:
http://developer.sugarcrm.com/2011/04/19/howto-add-a-search-field-that-searches-another-module/
http://www.lairdscomputer.com/Blog/tabid/62/EntryId/50/SugarCRM-Cross-Module-Search-Fields.aspx

While these gave a basic example implementation they seemed short on explanation of what the options mean and which are available. I found many more posts of others struggling to implement it.

I’ve only just started to use suitecrm so I had no background knowledge of how to customise the code. I’ll try and document what I found here.

Firstly you should only ever alter code under the <SUGAR_INSTALL>/custom/modules/ directory, never directly in the <SUGAR_INSTALL>/modules/ directory as your changes will be overwritten by future updates.

If you are customising the search you will need to know about the SQL DB table structure of the crm. I found this resource very handy:
http://apidocs.sugarcrm.com/schema/6.5.18/ce/index.html

You will also need to be able to see the SQL that the system generates to debug what is going on so you need to turn the logging level of your installation to info by going to the admin section ‘System Settings’ and at the bottom of the page change the ‘Log Level’ to ‘Info’. You can then watch the <SUGAR_INSTALL>/sugarcrm.log to see what SQL is being run.

So now we get to customising the search to search across another module’s tables. My requirement was to search from the Contacts module contacts associated with a custom module called ‘Product Associated’ (One-to-Many)

You need to edit the following:
<SUGAR_INSTALL>/custom/modules/Contacts/metadata/searchdefs.php
This file controls the search fields available in the basic and advanced search forms. In the ‘how to’ articles they show you how to implement a text based search field (‘type’ => ‘name’) but I want to search on a drop-down field in the related ‘Product Associated’ module. To do this you first need to import the list of available options at the top of the file by inserting:

global $app_list_strings;

then you can add your search field as a multiple select box by adding into either the ‘basic_search’ or ‘advanced_search’ array:

'consumer_prod' =>
      array (
        'name' => 'consumer_prod',
        'label' => 'Consumer Product',
        'type' => 'enum',
        'options' => $app_list_strings['consumer_products_c_list'],
        'displayParams' => array ('size'=>'5',),
        'default' => true,
        'width' => '10%',
      ),

where ‘consumer_products_c_list’ is the name of your drop-down field in the ‘Product Associated’ module.

Next you need to edit:
<SUGAR_INSTALL>/custom/modules/Contacts/metadata/SearchFields.php
This file contains the custom SQL you want to insert into main search query to perform the search on your other module’s tables.

This is where I depart company from the ‘how-to’ articles as they only way I could get my search to work was by using the ‘operator’=>‘subquery’ route that produced some really poor performing sql like this with multiple subqueries in the where clause using the ‘IN’ operator:

SELECT  contacts.id , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as name, contacts.first_name , contacts.last_name , contacts.salutation  , accounts.name account_name, jtl0.account_id account_id, contacts.title , contacts.phone_work  , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, contacts.date_entered , contacts.assigned_user_id  
FROM contacts  
LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c   
LEFT JOIN  accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0  
LEFT JOIN  users jt1 ON contacts.assigned_user_id=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 
where ((contacts.id IN (SELECT contacts.id FROM contacts INNER JOIN accoz_product_ass_business_contacts_c ON accoz_product_ass_business_contacts_c.accoz_product_ass_business_contactscontacts_ida=contacts.id INNER JOIN accoz_product_ass_business ON accoz_product_ass_business.id = accoz_product_ass_business_contacts_c.accoz_prodaec4usiness_idb where contacts.deleted=0 AND accoz_product_ass_business.accountz_product IN ('EV3')) ) AND ( contacts.id IN (SELECT contacts.id FROM contacts INNER JOIN accoz_product_ass_consumer_contacts_c ON accoz_product_ass_consumer_contacts_c.accoz_product_ass_consumer_contactscontacts_ida=contacts.id INNER JOIN accoz_product_ass_consumer ON accoz_product_ass_consumer.id = accoz_product_ass_consumer_contacts_c.accoz_prod4a9aonsumer_idb where contacts.deleted=0 AND accoz_product_ass_consumer.accountz_product IN ('HAZ_12')))) AND contacts.deleted=0 ORDER BY name ASC LIMIT 0,21

There is an ‘operator’=>‘innerjoin’ that looked like what I was after but I just could not get it working with enum values. To see a list of what ‘operator’ options are available you have to look at the code: https://github.com/sugarcrm/sugarcrm_dev/blob/master/include/SearchForm/SearchForm2.php#L979

Looking at the generated sql statement above I could see that there were already joins on the ‘account’ and ‘users’ tables. If only I could add my own joins in there I could simply stick a where clause in instead like this:

'consumer_prod' =>
  array (
    'query_type' => 'default',
    'operator'=>'in',
    'db_field' =>
    array (
      'accoz_product_ass_consumer.accountz_product',
    ),
  ),

So how to insert my own innerjoin. I searched around and finally came across this post:
http://stackoverflow.com/questions/26356355/sugarcrm-search-logic-for-the-leads-module

This led me to edit <SUGAR_INSTALL>/custom/modules/Contacts/views/view.list.php to override the ‘listViewProcess()’ method of the ‘ContactsViewList’ class like so:

function listViewProcess() {

        if(isset($_REQUEST['consumer_prod_advanced']))
        {
                $this->params['custom_from'] = ' INNER JOIN accoz_product_ass_consumer_contacts_c ON accoz_product_ass_consumer_contacts_c.accoz_product_ass_consumer_contactscontacts_ida=contacts.id INNER JOIN accoz_product_ass_consumer ON accoz_product_ass_consumer.id = accoz_product_ass_consumer_contacts_c.accoz_prod4a9aonsumer_idb ';
        }

        parent::listViewProcess();
    }

This basically is saying if the ‘consumer_prod_advanced’ form field is populated then add the inner join statement I need for my where clause (defined in SearchFields.php).

Is this a good solution? I don’t know. I know it works and produces much better sql queries like this:

SELECT  contacts.id , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as name, contacts.first_name , contacts.last_name , contacts.salutation  , accounts.name account_name, jtl0.account_id account_id, contacts.title , contacts.phone_work  , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, contacts.date_entered , contacts.assigned_user_id  
FROM contacts  
LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c   
LEFT JOIN  accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0  
LEFT JOIN  users jt1 ON contacts.assigned_user_id=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 
INNER JOIN accoz_product_ass_consumer_contacts_c ON accoz_product_ass_consumer_contacts_c.accoz_product_ass_consumer_contactscontacts_ida=contacts.id 
INNER JOIN accoz_product_ass_consumer ON accoz_product_ass_consumer.id = accoz_product_ass_consumer_contacts_c.accoz_prod4a9aonsumer_idb  
where ((accoz_product_ass_consumer.accountz_product in ('HV3'))) AND contacts.deleted=0 ORDER BY name ASC LIMIT 0,21

I’d welcome more experienced sugar/suite devs to warn me if I’ve done anything thats not update safe etc…

Sorry for the long post but I hope it helps others in some way.

Just found a problem with my cross module search implementation.

When performing a contact search to create a Tasklist from, the process for adding the matching contacts to a tasklist generates its own query that does not have the required db tables joined to it (causing a the query to error).

After digging around I found the code that generated the query in modules/ProspectLists/TargetListUpdate.php

I added some logic that checks the contents of the ‘where’ clause and if it contains one of my other module tables I append the extra join sql statements.

	if (strpos($ret_array['where'],'accoz_product_ass_consumer') !== FALSE)
		$ret_array['join'] .= ' INNER JOIN accoz_product_ass_consumer_contacts_c ON accoz_product_ass_consumer_contacts_c.accoz_product_ass_consumer_contactscontacts_ida=contacts.id INNER JOIN accoz_product_ass_consumer ON accoz_product_ass_consumer.id = accoz_product_ass_consumer_contacts_c.accoz_prod4a9aonsumer_idb ';

I don’t like copying the sql in multiple locations in the code base and I don’t think this is update safe but until I find a better way this will tide us over.

Hello,
can you please tell in which file i can print this query. I want to know core file where this query is executing