Hi all, I am trying to attempt a cross-module search (both BASIC and ADVANCED) and am running into some weird issues. Here are my complete files and the resulting MYSQL. Hope someone can help. Pleas ask questions if this is not enough information.
/custom/mopdules/nw_Contract/metadata/searchdefs.php
<?php
$module_name = 'nw_Contract';
$searchdefs [$module_name] = array(
'layout' =>
array(
'basic_search' =>
array(
'name' =>
array(
'name' => 'name',
'default' => true,
'width' => '10%',
),
'contract_status' =>
array(
'type' => 'enum',
'default' => true,
'studio' => 'visible',
'label' => 'LBL_CONTRACT_STATUS',
'width' => '10%',
'name' => 'contract_status',
),
'merchant' =>
array(
'type' => 'relate',
'studio' => 'visible',
'label' => 'LBL_MERCHANT',
'id' => 'NW_MERCHANT_ID_C',
'link' => true,
'width' => '10%',
'default' => true,
'name' => 'merchant',
),
'outstanding_balance' =>
array(
'type' => 'currency',
'label' => 'LBL_OUTSTANDING_BALANCE',
'currency_format' => true,
'width' => '10%',
'default' => true,
'name' => 'outstanding_balance',
),
'funding_status' =>
array(
'type' => 'enum',
'studio' => 'visible',
'label' => 'LBL_FUNDING_STATUS',
'width' => '10%',
'default' => true,
'name' => 'funding_status',
),
'default_payment' =>
array(
'type' => 'enum',
'studio' => 'visible',
'label' => 'LBL_DEFAULT_PAYMENT',
'width' => '10%',
'default' => true,
'name' => 'default_payment',
),
'created_by' =>
array(
'type' => 'assigned_user_name',
'label' => 'LBL_CREATED',
'width' => '10%',
'default' => true,
'name' => 'created_by',
),
'contract_type' =>
array(
'type' => 'enum',
'default' => true,
'studio' => 'visible',
'label' => 'LBL_CONTRACT_TYPE',
'width' => '10%',
'name' => 'contract_type',
),
'start_range_last_payment_date' =>
array(
'type' => 'date',
'label' => 'LBL_START_LAST_PAYMENT_DATE',
'width' => '10%',
'default' => true,
'name' => 'start_range_last_payment_date',
),
'end_range_last_payment_date' =>
array(
'type' => 'date',
'label' => 'LBL_END_LAST_PAYMENT_DATE',
'width' => '10%',
'default' => true,
'name' => 'end_range_last_payment_date',
),
'start_range_funded_date' =>
array(
'type' => 'date',
'label' => 'LBL_START_FUNDED_DATE',
'width' => '10%',
'default' => true,
'name' => 'start_range_funded_date',
),
'end_range_funded_date' =>
array(
'type' => 'date',
'label' => 'LBL_END_FUNDED_DATE',
'width' => '10%',
'default' => true,
'name' => 'end_range_funded_date',
),
'iso' =>
array(
'type' => 'relate',
'studio' => 'visible',
'label' => 'LBL_ISO',
'id' => 'NW_ISO_ID_C',
'link' => true,
'width' => '10%',
'default' => true,
'name' => 'iso',
),
// This is the field I added and am trying to search on
'iso_type_c' =>
array(
'label' => 'LBL_CONTRACT_ISO_TYPE',
'type' => 'enum',
'default' => true,
'link' => false,
'studio' => 'visible',
'width' => '10%',
'name' => 'iso_type_c',
),
),
'advanced_search' =>
array(
'name' =>
array(
'name' => 'name',
'default' => true,
'width' => '10%',
),
'contract_status' =>
array(
'type' => 'enum',
'default' => true,
'studio' => 'visible',
'label' => 'LBL_CONTRACT_STATUS',
'width' => '10%',
'name' => 'contract_status',
),
'outstanding_balance' =>
array(
'type' => 'currency',
'label' => 'LBL_OUTSTANDING_BALANCE',
'currency_format' => true,
'width' => '10%',
'default' => true,
'name' => 'outstanding_balance',
),
'merchant' =>
array(
'type' => 'relate',
'studio' => 'visible',
'label' => 'LBL_MERCHANT',
'id' => 'NW_MERCHANT_ID_C',
'link' => true,
'width' => '10%',
'default' => true,
'name' => 'merchant',
),
'merchant_approval' =>
array(
'type' => 'enum',
'default' => true,
'studio' => 'visible',
'label' => 'LBL_MERCHANT_APPROVAL',
'width' => '10%',
'name' => 'merchant_approval',
),
'funding_status' =>
array(
'type' => 'enum',
'studio' => 'visible',
'label' => 'LBL_FUNDING_STATUS',
'width' => '10%',
'default' => true,
'name' => 'funding_status',
),
'underwriting_status' =>
array(
'type' => 'enum',
'default' => true,
'studio' => 'visible',
'label' => 'LBL_UNDERWRITING_STATUS',
'width' => '10%',
'name' => 'underwriting_status',
),
'split_percentage' =>
array(
'type' => 'decimal',
'label' => 'LBL_SPLIT_PERCENTAGE',
'width' => '10%',
'default' => true,
'name' => 'split_percentage',
),
'split_effective_date' =>
array(
'type' => 'datetimecombo',
'label' => 'LBL_SPLIT_EFFECTIVE_DATE',
'width' => '10%',
'default' => true,
'name' => 'split_effective_date',
),
'factor_rate' =>
array(
'type' => 'decimal',
'label' => 'LBL_FACTOR_RATE',
'width' => '10%',
'default' => true,
'name' => 'factor_rate',
),
'assigned_user_name' =>
array(
'link' => true,
'type' => 'relate',
'label' => 'LBL_ASSIGNED_TO_NAME',
'width' => '10%',
'default' => true,
'id' => 'ASSIGNED_USER_ID',
'name' => 'assigned_user_name',
),
'created_by' =>
array(
'type' => 'assigned_user_name',
'label' => 'LBL_CREATED',
'width' => '10%',
'default' => true,
'name' => 'created_by',
),
'date_entered' =>
array(
'type' => 'datetime',
'label' => 'LBL_DATE_ENTERED',
'width' => '10%',
'default' => true,
'name' => 'date_entered',
),
// This is the field I added and am trying to search on
'iso_type_c' =>
array(
'label' => 'LBL_CONTRACT_ISO_TYPE',
'type' => 'enum',
'default' => true,
'studio' => 'visible',
'width' => '10%',
'name' => 'iso_type_c',
),
'last_payment_date' =>
array(
'type' => 'datetimecombo',
'label' => 'LBL_LAST_PAYMENT_DATE',
'width' => '10%',
'default' => true,
'name' => 'last_payment_date',
),
),
),
'templateMeta' =>
array(
'maxColumns' => '3',
'maxColumnsBasic' => '4',
'widths' =>
array(
'label' => '10',
'field' => '30',
),
),
);
?>custom/modules/nw_Contract/metdata/SearchFields.php
<?php
// created: 2016-12-21 10:12:45
$searchFields['nw_Contract'] = array(
'name' =>
array(
'query_type' => 'default',
),
'current_user_only' =>
array(
'query_type' => 'default',
'db_field' =>
array(
0 => 'assigned_user_id',
),
'my_items' => true,
'vname' => 'LBL_CURRENT_USER_FILTER',
'type' => 'bool',
),
'assigned_user_id' =>
array(
'query_type' => 'default',
),
'range_funded_date' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'start_range_funded_date' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'end_range_funded_date' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'range_last_payment_date' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'start_range_last_payment_date' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'end_range_last_payment_date' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'range_date_entered' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'start_range_date_entered' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'end_range_date_entered' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'range_date_modified' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'start_range_date_modified' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'end_range_date_modified' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
[color=#0044ff] 'iso_type_c' =>
array(
'query_type' => 'default',
'operator' => 'subquery',
'subquery' => 'SELECT id FROM nw_iso i WHERE i.deleted = 0 AND i.iso_type LIKE',
'db_field' => array('nw_iso_id_c')
),[/color]
'range_split_effective_date' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'start_range_split_effective_date' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
'end_range_split_effective_date' =>
array(
'query_type' => 'default',
'enable_range_search' => true,
'is_date_field' => true,
),
);
Raw mySQL Result
[size=5]SELECT nw_contract.id , nw_contract.nw_merchant_id_c , nw_contract.nw_iso_id_c , nw_contract.name , jt0.name merchant , jt1.name iso , nw_contract.purchase_amount , nw_contract.outstanding_balance , nw_contract.contract_status , nw_contract.underwriting_status , nw_contract.last_payment_date , nw_contract.funding_status , nw_contract.iso_type_c , nw_contract.assigned_user_id FROM nw_contract LEFT JOIN nw_contract_cstm ON nw_contract.id = nw_contract_cstm.id_c LEFT JOIN nw_merchant jt0 ON nw_contract.nw_merchant_id_c = jt0.id AND jt0.deleted=0 LEFT JOIN nw_iso jt1 ON nw_contract.nw_iso_id_c = jt1.id AND jt1.deleted=0 where ((nw_contract.nw_iso_id_c IN (select id from ([color=#ff4400]SELECT id FROM nw_iso i WHERE i.deleted = 0 AND i.iso_type LIKE ‘‘internal’%’[/color]) jt1.name_type_c_derived))) AND nw_contract.deleted=0 ORDER BY nw_contract.date_entered DESC LIMIT 0,3[/size]1
The problem is in the iso_type_c cross-module search. Notice how, in the result SQL, the ENUM (internal) is double quoted? And why is an extra table being derived after the subquery?