AnsweredAssumed Answered

Web Service get_relationships does not work with related_module_query parameter in custom fields

Question asked by ananth k on Mar 16, 2015
I'm using sugarcrm CE version 6.5.8. In this version , Web Service get_relationships does not work with related_module_query parameter in custom fields. I'm making a REST API Call to get contacts related to the accounts module. I'm filtering the contact based on a custom field (ts_c) in the contacts module.

NOTE : I'm also checking the custom field (ts_c) of contacts is equal to a particular value ( "1" ) in that REST CALL. 

CODE  : 
 
$get_relationships_parameters = array(

         'session'=>$session_id,

         //The name of the module from which to retrieve records.
         'module_name' => 'Accounts',

         //The ID of the specified module bean.
         'module_id' => '40972bcf-612b-bc83-f695-5502be11215d',

         //The relationship name of the linked field from which to return records.
         'link_field_name' => 'contacts',

         //The portion of the WHERE clause from the SQL statement used to find the related items.
       
  'related_module_query' => '   contacts_cstm.ts_c IS NOT NULL and  contacts_cstm.ts_c = "1"  ',
 

         //The related fields to be returned.
  'related_fields' => array(
   'first_name','last_name' 
   ),

         //For every related bean returned, specify link field names to field information.
         'related_module_link_name_to_fields_array' => array(
         ),

         //To exclude deleted records
         'deleted'=> '0',

         //order by
         'order_by' => '',

         //offset
         'offset' => 0,

         //limit
         'limit' => 5,
    );

$get_relationships_result = call("get_relationships", $get_relationships_parameters, $url);



This example is working fine in SugarCRM CE version 6.5.9, it is not working in SugarCRM Professional version 6.5.8.

I figured out the issue. I'm not able figure out the solution. 
The issue is in version 6.5.8 , it is creating a query without custom fields table , so i'm not able to filter the data based on the custom query

Query generated in Version 6.5.8 professional : 

SELECT contact_id id FROM accounts_contacts, contacts WHERE accounts_contacts.account_id = '40972bcf-612b-bc83-f695-5502be11215d76b6bfc9-0398-7e59-7a41-53ad1af26f52'
AND accounts_contacts.contact_id=contacts.id
AND   contacts_cstm.ts_c IS NOT NULL AND  contacts_cstm.ts_c = "1"  AND accounts_contacts.deleted=0


Query generated in Version 6.5.17 CE: 

SELECT contact_id id FROM accounts_contacts , contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c  WHERE accounts_contacts.account_id = '40972bcf-612b-bc83-f695-5502be11215d' AND accounts_contacts.contact_id=contacts.id AND    account_id  IN  ( "40972bcf-612b-bc83-f695-5502be11215d" ) and contacts_cstm.ts_c IS NULL   AND accounts_contacts.deleted=0 LIMIT 0,5


Any help would be greatly appreciated. 

Outcomes