I am having the same problem. Was a solution found for this?
I think some of the vardefs for this Module may be incorrect because I had some difficulty getting the relate type to work but here is what I ended up using.
PHP Code:
// Note: I have narrowed this list down
$dictionary['my_User_Reports'] = array(
'fields'=>array (
'direction' =>
array (
'required' => false,
'name' => 'direction',
'vname' => 'LBL_DIRECTION',
'type' => 'enum',
'massupdate' => 0,
'default' => 'Initial Caller',
'comments' => '',
'help' => '',
'importable' => 'true',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => '0',
'audited' => false,
'reportable' => true,
'len' => 100,
'size' => '20',
'options' => 'call_contact_direction_list',
'studio' => 'visible',
'dependency' => false,
),
'my_users_id_c' =>
array (
'required' => false,
'name' => 'my_users_id_c',
'vname' => '',
'type' => 'id',
'massupdate' => 0,
'comments' => '',
'help' => '',
'importable' => 'true',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => 0,
'audited' => false,
'reportable' => true,
'len' => 36,
'size' => '20',
),
'my_user' =>
array (
'required' => false,
'source' => 'non-db',
'name' => 'my_user',
'vname' => 'LBL_MY_USER',
'type' => 'relate', // relate type
'massupdate' => 0,
'comments' => '',
'help' => '',
'importable' => 'true',
'duplicate_merge' => 'disabled',
'duplicate_merge_dom_value' => '0',
'audited' => false,
'reportable' => true,
'len' => '255',
'size' => '20',
'id_name' => 'my_users_id_c', // link with my_users_id_c
'ext2' => 'my_Users', // Not sure what this does
'module' => 'my_Users', // Module to link to
'rname' => 'name', // Not sure what this does
'quicksearch' => 'enabled',
'studio' => 'visible',
)
);
Code:
-- This is the query that is being generated by sugar
SELECT my_user_reports.id , my_user_reports.name , jt0.name my_user , my_user_reports.direction ,
my_user_reports.status , TRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))
contact_id , my_user_reports.phone , my_user_reports.appointment_date , jt2.name appointment_with ,
my_user_reports.date_entered , my_user_reports.assigned_user_id
FROM my_user_reports
LEFT JOIN my_users jt0 ON my_user_reports.my_user_id_c = jt0.id AND jt0.deleted=0
LEFT JOIN contacts jt1 ON my_user_reports.contact_id_c = jt1.id AND jt1.deleted=0
LEFT JOIN my_users jt2 ON my_user_reports.my_user_id1_c = jt2.id AND jt2.deleted=0
WHERE ((jt0.name_reports.direction IN ('Recruiter')))
AND my_user_reports.deleted=0
ORDER BY my_user_reports.name ASC;
-- The problem is in the where - this is what it should be
WHERE ((my_user_reports.direction IN ('Recruiter')))
AND my_user_reports.deleted=0
ORDER BY my_user_reports.name ASC; The problem is occurring within the where during the preg_replace on line 3118 of [sugarcrm]/data/SugarBean.php
my_user_reports.direction is getting changed to jt0.name_reports.direction
PHP Code:
// Here is the if statement
if ($data['type'] == 'relate' && (isset($data['custom_module']) || isset($data['ext2']))) {
$joinTableAlias = 'jt' . $jtcount;
$relateJoinInfo = $this->custom_fields->getRelateJoin($data, $joinTableAlias);
$ret_array['select'] .= $relateJoinInfo['select'];
$ret_array['from'] .= $relateJoinInfo['from'];
//Replace any references to the relationship in the where clause with the new alias
//If the link isn't set, assume that search used the local table for the field
$searchTable = isset($data['link']) ? $relateJoinInfo['rel_table'] : $this->table_name;
$field_name = $relateJoinInfo['rel_table'] . '.' . !empty($data['name'])?$data['name']:'name';
$where = preg_replace('/(^|[\s(])' . $field_name . '/' , '${1}' . $relateJoinInfo['name_field'], $where); // problem occurs here
$jtcount++;
}
LinkBack URL
About LinkBacks
list view generated wrong query
Reply With Quote
Bookmarks