在Sugarcrm(SuiteCRM)中进行复杂的跨模块搜索

Question

我想在至少2个模块中的许多DB字段中按字段电话号码进行搜索。我有解决方案，但是它没有优化和非常慢。

我的解决方案：

自定义/模块/帐户/元数据/searchdefs.php

 'phone' => array (
    'name' => 'phone',
    'label' => 'LBL_ANY_PHONE',
    'type' => 'name',
    'default' => true,
    'width' => '10%',
  ),

自定义/模块/帐户/元数据/searchfields.php

'phone' => array(
  'query_type' => 'default',
  'operator' => 'subquery',
  'subquery' => array(
    'SELECT id FROM accounts a LEFT JOIN accounts_cstm ac ON a.id = ac.id_c
     WHERE returnNumericOnly(phone_office_mask_c) LIKE',
    'SELECT id FROM accounts a LEFT JOIN accounts_cstm ac ON a.id = ac.id_c
     WHERE returnNumericOnly(phone_fax_mask_c) LIKE',
    'SELECT id FROM accounts a LEFT JOIN accounts_cstm ac ON a.id = ac.id_c
     WHERE returnNumericOnly(phone_alternate_mask_c) LIKE',
    'SELECT acc.id FROM accounts acc
       LEFT JOIN accounts_contacts ac_cont ON acc.id = ac_cont.account_id AND ac_cont.deleted = 0
       LEFT JOIN contacts cont ON ac_cont.contact_id = cont.id AND cont.deleted = 0
       LEFT JOIN contacts_cstm cont_c ON cont.id = cont_c.id_c
     WHERE returnNumericOnly(cont_c.phone_mobile_mask_c) LIKE',
    'SELECT acc.id FROM accounts acc
       LEFT JOIN accounts_contacts ac_cont ON acc.id = ac_cont.account_id AND ac_cont.deleted = 0
       LEFT JOIN contacts cont ON ac_cont.contact_id = cont.id AND cont.deleted = 0
       LEFT JOIN contacts_cstm cont_c ON cont.id = cont_c.id_c
     WHERE returnNumericOnly(cont_c.phone_work_mask_c) LIKE',
    'SELECT acc.id FROM accounts acc
       LEFT JOIN accounts_contacts ac_cont ON acc.id = ac_cont.account_id AND ac_cont.deleted = 0
       LEFT JOIN contacts cont ON ac_cont.contact_id = cont.id AND cont.deleted = 0
       LEFT JOIN contacts_cstm cont_c ON cont.id = cont_c.id_c
     WHERE returnNumericOnly(cont_c.phone_fax_mask_c) LIKE',
    'OR' => true
  ),
  'db_field' => array('id')
),

但是结果SQL类似：

    SELECT  *
FROM accounts
 LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c
 LEFT JOIN ea1_lead_source jt0 ON accounts_cstm.ea1_lead_source_id_c = jt0.id AND jt0.deleted=0
 LEFT JOIN  users jt1 ON accounts.created_by=jt1.id AND jt1.deleted=0 AND jt1.deleted=0
 LEFT JOIN ea1_region jt2 ON accounts_cstm.ea1_region_id_c = jt2.id AND jt2.deleted=0
WHERE
((
accounts.id IN (
  SELECT id FROM accounts a LEFT JOIN accounts_cstm ac ON a.id = ac.id_c
        WHERE phone_office_mask_c LIKE '749577%') 
OR  accounts.id IN (
  SELECT id FROM accounts a LEFT JOIN accounts_cstm ac ON a.id = ac.id_c
        WHERE phone_fax_mask_c LIKE '749577%')
OR  accounts.id IN (
  SELECT id FROM accounts a LEFT JOIN accounts_cstm ac ON a.id = ac.id_c
        WHERE phone_alternate_mask_c LIKE '749577%')
OR  accounts.id IN (
  SELECT acc.id FROM accounts acc LEFT JOIN accounts_contacts ac_cont ON acc.id = ac_cont.account_id AND ac_cont.deleted = 0
          LEFT JOIN contacts cont ON ac_cont.contact_id = cont.id AND cont.deleted = 0
          LEFT JOIN contacts_cstm cont_c ON cont.id = cont_c.id_c
        WHERE returnNumericOnly(cont_c.phone_mobile_mask_c) LIKE '749577%')
OR  accounts.id IN (
  SELECT acc.id FROM accounts acc
          LEFT JOIN accounts_contacts ac_cont ON acc.id = ac_cont.account_id AND ac_cont.deleted = 0
          LEFT JOIN contacts cont ON ac_cont.contact_id = cont.id AND cont.deleted = 0
          LEFT JOIN contacts_cstm cont_c ON cont.id = cont_c.id_c
        WHERE returnNumericOnly(cont_c.phone_work_mask_c) LIKE '749577%')
OR  accounts.id IN (
  SELECT acc.id FROM accounts acc
          LEFT JOIN accounts_contacts ac_cont ON acc.id = ac_cont.account_id AND ac_cont.deleted = 0
          LEFT JOIN contacts cont ON ac_cont.contact_id = cont.id AND cont.deleted = 0
          LEFT JOIN contacts_cstm cont_c ON cont.id = cont_c.id_c
        WHERE returnNumericOnly(cont_c.phone_fax_mask_c) LIKE '749577%')
))
AND accounts.deleted=0
ORDER BY accounts.name ASC

我想要结果搜索SQL而不子征服，类似于：

    SELECT *
FROM accounts acc
  LEFT JOIN accounts_cstm acc_c ON accounts.id = accounts_cstm.id_c
  LEFT JOIN accounts_contacts ac_cont ON acc.id = ac_cont.account_id AND ac_cont.deleted = 0
  LEFT JOIN contacts cont ON ac_cont.contact_id = cont.id AND cont.deleted = 0
  LEFT JOIN contacts_cstm cont_c ON cont.id = cont_c.id_c
  LEFT JOIN ...
WHERE 
  acc_c.phone_office_mask_c LIKE '749577%'
  AND acc_c.phone_fax_mask_c LIKE '749577%'
  AND acc_c.phone_alternate_mask_c LIKE '749577%'
  AND cont_c.phone_mobile_mask_c LIKE '749577%'
  AND cont_c.phone_work_mask_c LIKE '749577%'
  AND cont_c.phone_fax_mask_c LIKE '749577%'
  AND ….

可以做到吗？

Answer 1

如果您使用的是子查询操作员，它将进行子查询。

您可以在此处从源代码获得更多信息include/SearchForm/SearchForm2.php#L1164

例如，在联系人上搜索了任何电话搜索，为什么不将其用作基础？