Using subquery for search in popup view

Question

I am using SugarCrm CE 6.5.20, Out the box it does not allow searching from related modules with a many to many relationship. But this can be overcome by adding a custom relate field that respresents your relationship then using a custom subquery in SearchFields.php like so:

'par_med_cond' => 
 array (
   'query_type' => 'default',
   'operator' => 'subquery',
    subquery' => 'SELECT spsmc.frp_fundraisingprogram_contacts_1contacts_idb FROM frp_fundraisingprogram_contacts_1_c spsmc LEFT JOIN frp_fundraisingprogram smc ON spsmc.frp_fundraisingprogram_contacts_1frp_fundraisingprogram_ida = smc.id WHERE smc.deleted =0 AND spsmc.deleted =0 AND smc.name LIKE',
   'db_field' => 
        array (
           0 => 'id',
        ),
   ),

This works on a normal listview advanced search panel but does not work when adding the field to popupview search.

Its not even adding the subquery on at all in the popup. This is the query that's failing:

 SELECT  contacts.id ,contacts_cstm.contactkeyhost_c, 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.profile_ref , 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 where (( par_med_cond like 'mtest1%' )) AND contacts.deleted=0 ORDER BY name ASC LIMIT 0,21  - Unknown column 'par_med_cond' in 'where clause'

Answer 1

I had to customize the view.popup.php for the module and jam in the condition if being searched on in $popupMeta['whereStatement'] within display().

 if(!empty($_REQUEST['mode']) && $_REQUEST['mode']=='MultiSelect_fp')

{ $_REQUEST['mode'] = 'MultiSelect';

$popupMeta['whereStatement'] .= (!empty($popupMeta['whereStatement']) ? ' AND' : '')." ((contacts.id IN (select frp_fundraisingprogram_contacts_1contacts_idb from (SELECT spsmc.frp_fundraisingprogram_contacts_1contacts_idb FROM frp_fundraisingprogram_contacts_1_c spsmc LEFT JOIN frp_fundraisingprogram smc ON spsmc.frp_fundraisingprogram_contacts_1frp_fundraisingprogram_ida = smc.id WHERE smc.deleted =0 AND spsmc.deleted =0 AND smc.name LIKE '".$_REQUEST['metadata']."') par_med_cond_derived))) AND contacts.deleted=0 ";
}