在弹出式视图中使用子查询进行搜索

Question

我使用的是SugarCrm CE 6.5.20，开箱即用，它不允许从具有多对多关系的相关模块中搜索。但这可以通过添加一个表示关系的自定义关联字段来克服，然后在SearchFields.php中使用自定义子查询，如下所示：

'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',
        ),
   ),

这在普通的列表视图高级搜索面板上有效，但在将字段添加到弹出视图搜索时不起作用。

它甚至没有在弹出窗口中添加子查询。以下是失败的查询：

 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

如果在display()中的$popupMeta‘’whereStatement‘中被搜索到，我必须为模块定制view.popup.php，并在条件中进行jam。

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

{$_REQUEST‘’mode‘=’多选‘；

$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 ";
}