Contacts from SugarCRM CE by email address

SurgarCRM CE (Community Edition) is an open source client relation management software. Recently I had the… I am searching for the right word… is it pleasure, no; opportunity; no that isn’t it either. I was looking for information on how to use SugarCRM CE’s REST API to get contact information based on the contact’s e-mail address. I think the word is “painful” experience. But, eventually I was successful so I thought I would share my experience.

What is REST?

Basically it is a way of getting information from a web service. In the case of SugarCRM it is a way of sending predefined requests (such as login, or get_entry_list) to SugarCRM and have returned data (such as a login session id, or a list of data from the database). What makes it nice is that it uses JSON (JavaScript Object Notation) instead of XML. The REST requests are simply HTML POSTS sending JSON data to the SugarCRM REST API URL, and getting returned a JSON object with the data.

Now that everyone who is not a developer has left this article, lets continue.

Let me start by saying the SugarCRM CE documentation is woefully inadequate, compared to PHP documentation or WordPress documentation. This is probably due to a much smaller user community. You can do web searches, but you don’t find much. I am used to finding long threads of discussion with copious examples when searching on other topics, but with SugarCRM I found most threads consist of short discussions and links to the same example.

Here is the short version of what I wanted to do. Access the Sugar database, passing an email address, and getting returned the contact information of the user with that email address. Seems like something many people would want to do. So lets get started.

The first thing you have to do is to make a REST API call to log in to Sugar. Since we are going to be sending multiple API calls it makes sense to create a function for the actual REST call. We are coding this in PHP and to make an HTTP request in PHP we will use curl. Here is the function:

/**
 * Function Name: sugar_http_request
 * Description: Makes an HTTP POST request using CURL to a given URL
 */
function sugar_http_request($url, $postArgs){
  // Open a curl session for making the call 
    $curl = curl_init($url); 
    // Tell curl to use HTTP POST 
    curl_setopt($curl, CURLOPT_POST, true); 
    // Tell curl not to return headers, but do return the response 
    curl_setopt($curl, CURLOPT_HEADER, false); 
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); 
    curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs); 
    // Make the call, returning the result 
    $response = curl_exec( $curl );    
    // Close the connection 
    curl_close( $curl );  
    
    return $response;    
}

/**

* Function Name: sugar_http_request

* Description: Makes an HTTP POST request using CURL to a given URL

function sugar_http_request($url, $postArgs){

// Open a curl session for making the call

$curl = curl_init($url);

// Tell curl to use HTTP POST

curl_setopt($curl, CURLOPT_POST, true);

// Tell curl not to return headers, but do return the response

curl_setopt($curl, CURLOPT_HEADER, false);

curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);

curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);

// Make the call, returning the result

$response = curl_exec( $curl );

// Close the connection

curl_close( $curl );

return $response;

}

Don’t worry about understanding this code, treat it like a black box. All it is doing is making a HTML POST call to the URL, sending some POST data, and then returning the results back. The main thing you need to be concerned with is that your installation of PHP needs to have curl. Also the code above will not work if your REST URL uses https. Read the curl documentation if you need to use https.

Now we are ready for the first step. Logging in and getting a session ID back that we can use for subsequent REST calls.

First define the path to the REST API.

$sugarcrm_api_url = "http://your_sugar_domain/service/v4_1/rest.php";

1	$sugarcrm_api_url = "http://your_sugar_domain/service/v4_1/rest.php";

To login you need to have your admin account name and password, and the URL to the REST API interface. You can use any account name and password, but using an account that is an admin account will let us do more.

Note: if you go to the URL directly in a web browser it will return the class definitions and methods you can use. With Sugar CE the documentation is confusing, since it references SOAP errors even though this is a REST API. However it does describe the parameters so it is good to take a look at it.

So we set up a parameters array to hold the account information;

    /* login to SugarCRM */
    $parameters = array(
        'user_auth' => array(
                'user_name' => 'your_account_name', 
                'password' => md5('your_account_password'), 
        ),
    );

/* login to SugarCRM */

$parameters = array(

'user_auth' => array(

'user_name' => 'your_account_name',

'password' => md5('your_account_password'),

);

Now JSON encode that data and add it to the other post arguments:

  $json = json_encode($parameters);
  $postArgs  = 'method=login&amp;';
  $postArgs .= 'input_type=JSON&amp;';
  $postArgs .= 'response_type=JSON&amp;';
  $postArgs .= 'rest_data=' . $json;

$json = json_encode($parameters);

$postArgs = 'method=login&';

$postArgs .= 'input_type=JSON&';

$postArgs .= 'response_type=JSON&';

$postArgs .= 'rest_data=' . $json;

Notice the “method.” It is one of the predefined Sugar REST API commands that you can make. So now make the REST request and get back the data:

  $response = sugar_http_request($sugarcrm_api_url, $postArgs);
  /* Convert the result from JSON format to a PHP array */
  $result = json_decode( $response );

$response = sugar_http_request($sugarcrm_api_url, $postArgs);

/* Convert the result from JSON format to a PHP array */

$result = json_decode( $response );

If you run this code and do a print_r($results) you should see something like:

string(938) "{"id":"ep40179oqoukpqgaaahnfmv8h0","module_name":"Users","name_value_list":{"user_id":{"name":"user_id","value":"seed_jim_id"},"user_name":{"name":"user_name","value":"bob"},"user_language":{"name":"user_language","value":"en_us"},"user_currency_id":{"name":"user_currency_id","value":"-99"},"user_is_admin":{"name":"user_is_admin","value":true},"user_default_team_id":{"name":"user_default_team_id","value":null},"user_default_dateformat":{"name":"user_default_dateformat","value":"m\/d\/Y"},"user_default_timeformat":{"name":"user_default_timeformat","value":"h:ia"},"user_number_seperator":{"name":"user_number_seperator","value":","},"user_decimal_seperator":{"name":"user_decimal_seperator","value":"."},"mobile_max_list_entries":{"name":"mobile_max_list_entries","value":null},"mobile_max_subpanel_entries":{"name":"mobile_max_subpanel_entries","value":null},"user_currency_name":{"name":"user_currency_name","value":"US Dollars"}}}"
object(stdClass)#1 (3) {
  ["id"]=>
  string(26) "ep40179oqoukpqgaaahnfmv8h0"
  ["module_name"]=>
  string(5) "Users"
  ["name_value_list"]=>
  object(stdClass)#2 (13) {
    ["user_id"]=>
    object(stdClass)#3 (2) {
      ["name"]=>
      string(7) "user_id"
      ["value"]=>
      string(11) "seed_jim_id"
    }
    ["user_name"]=>
    object(stdClass)#4 (2) {
      ["name"]=>
      string(9) "user_name"
      ["value"]=>
      string(3) "bob"
    }
    ["user_language"]=>
    object(stdClass)#5 (2) {
      ["name"]=>
      string(13) "user_language"
      ["value"]=>
      string(5) "en_us"
    }
    ["user_currency_id"]=>
    object(stdClass)#6 (2) {
      ["name"]=>
      string(16) "user_currency_id"
      ["value"]=>
      string(3) "-99"
    }
    ["user_is_admin"]=>
    object(stdClass)#7 (2) {
      ["name"]=>
      string(13) "user_is_admin"
      ["value"]=>
      bool(true)
    }
    ["user_default_team_id"]=>
    object(stdClass)#8 (2) {
      ["name"]=>
      string(20) "user_default_team_id"
      ["value"]=>
      NULL
    }
    ["user_default_dateformat"]=>
    object(stdClass)#9 (2) {
      ["name"]=>
      string(23) "user_default_dateformat"
      ["value"]=>
      string(5) "m/d/Y"
    }
    ["user_default_timeformat"]=>
    object(stdClass)#10 (2) {
      ["name"]=>
      string(23) "user_default_timeformat"
      ["value"]=>
      string(4) "h:ia"
    }
    ["user_number_seperator"]=>
    object(stdClass)#11 (2) {
      ["name"]=>
      string(21) "user_number_seperator"
      ["value"]=>
      string(1) ","
    }
    ["user_decimal_seperator"]=>
    object(stdClass)#12 (2) {
      ["name"]=>
      string(22) "user_decimal_seperator"
      ["value"]=>
      string(1) "."
    }
    ["mobile_max_list_entries"]=>
    object(stdClass)#13 (2) {
      ["name"]=>
      string(23) "mobile_max_list_entries"
      ["value"]=>
      NULL
    }
    ["mobile_max_subpanel_entries"]=>
    object(stdClass)#14 (2) {
      ["name"]=>
      string(27) "mobile_max_subpanel_entries"
      ["value"]=>
      NULL
    }
    ["user_currency_name"]=>
    object(stdClass)#15 (2) {
      ["name"]=>
      string(18) "user_currency_name"
      ["value"]=>
      string(10) "US Dollars"
    }
  }
}

100

101

string(938) "{"id":"ep40179oqoukpqgaaahnfmv8h0","module_name":"Users","name_value_list":{"user_id":{"name":"user_id","value":"seed_jim_id"},"user_name":{"name":"user_name","value":"bob"},"user_language":{"name":"user_language","value":"en_us"},"user_currency_id":{"name":"user_currency_id","value":"-99"},"user_is_admin":{"name":"user_is_admin","value":true},"user_default_team_id":{"name":"user_default_team_id","value":null},"user_default_dateformat":{"name":"user_default_dateformat","value":"m\/d\/Y"},"user_default_timeformat":{"name":"user_default_timeformat","value":"h:ia"},"user_number_seperator":{"name":"user_number_seperator","value":","},"user_decimal_seperator":{"name":"user_decimal_seperator","value":"."},"mobile_max_list_entries":{"name":"mobile_max_list_entries","value":null},"mobile_max_subpanel_entries":{"name":"mobile_max_subpanel_entries","value":null},"user_currency_name":{"name":"user_currency_name","value":"US Dollars"}}}"

object(stdClass)#1 (3) {

["id"]=>

string(26) "ep40179oqoukpqgaaahnfmv8h0"

["module_name"]=>

string(5) "Users"

["name_value_list"]=>

object(stdClass)#2 (13) {

["user_id"]=>

object(stdClass)#3 (2) {

["name"]=>

string(7) "user_id"

["value"]=>

string(11) "seed_jim_id"

}

["user_name"]=>

object(stdClass)#4 (2) {

["name"]=>

string(9) "user_name"

["value"]=>

string(3) "bob"

}

["user_language"]=>

object(stdClass)#5 (2) {

["name"]=>

string(13) "user_language"

["value"]=>

string(5) "en_us"

}

["user_currency_id"]=>

object(stdClass)#6 (2) {

["name"]=>

string(16) "user_currency_id"

["value"]=>

string(3) "-99"

}

["user_is_admin"]=>

object(stdClass)#7 (2) {

["name"]=>

string(13) "user_is_admin"

["value"]=>

bool(true)

}

["user_default_team_id"]=>

object(stdClass)#8 (2) {

["name"]=>

string(20) "user_default_team_id"

["value"]=>

NULL

}

["user_default_dateformat"]=>

object(stdClass)#9 (2) {

["name"]=>

string(23) "user_default_dateformat"

["value"]=>

string(5) "m/d/Y"

}

["user_default_timeformat"]=>

object(stdClass)#10 (2) {

["name"]=>

string(23) "user_default_timeformat"

["value"]=>

string(4) "h:ia"

}

["user_number_seperator"]=>

object(stdClass)#11 (2) {

["name"]=>

string(21) "user_number_seperator"

["value"]=>

string(1) ","

}

["user_decimal_seperator"]=>

object(stdClass)#12 (2) {

["name"]=>

string(22) "user_decimal_seperator"

["value"]=>

string(1) "."

}

["mobile_max_list_entries"]=>

object(stdClass)#13 (2) {

["name"]=>

string(23) "mobile_max_list_entries"

["value"]=>

NULL

}

["mobile_max_subpanel_entries"]=>

object(stdClass)#14 (2) {

["name"]=>

string(27) "mobile_max_subpanel_entries"

["value"]=>

NULL

}

["user_currency_name"]=>

object(stdClass)#15 (2) {

["name"]=>

string(18) "user_currency_name"

["value"]=>

string(10) "US Dollars"

}

The key data item you need for further REST calls is the id value, so we get it and save it in a variable:

$sessionId = $result->id;

1	$sessionId = $result->id;

Not too bad so far. Now to make a REST call to search for the email address. Here is where it gets difficult. There is a REST method “get_entry_list” which you should go read about. Go ahead and look at the Sugar documentation on it. I will wait.

Notice that there is only one example and that the description of parameters says nothing about parameters being required or optional. Now look at your rest.php page. I can’t give you a link to that but the path should be like:

http://your_sugar_domain/service/v4_1/rest.php

1	http://your_sugar_domain/service/v4_1/rest.php

but with your path to your SugarCRM installation. Below is the part on “get_entry_list”:

/**
     * Retrieve a list of beans.  This is the primary method for getting list of SugarBeans from Sugar using the SOAP API.
     *
     * @param String $session -- Session ID returned by a previous call to login.
     * @param String $module_name -- The name of the module to return records from.  This name should be the name the module was developed under (changing a tab name is studio does not affect the name that should be passed into this method)..
     * @param String $query -- SQL where clause without the word 'where'
     * @param String $order_by -- SQL order by clause without the phrase 'order by'
     * @param integer $offset -- The record offset to start from.
     * @param Array  $select_fields -- A list of the fields to be included in the results. <span style="color: #800000;"><strong>This optional parameter</strong></span> allows for only needed fields to be retrieved.
     * @param Array $link_name_to_fields_array -- A list of link_names and for each link_name, what fields value to be returned. For ex.'link_name_to_fields_array' => array(array('name' =>  'email_addresses', 'value' => array('id', 'email_address', 'opt_out', 'primary_address')))
     * @param integer $max_results -- The maximum number of records to return.  The default is the sugar configuration value for 'list_max_entries_per_page'
     * @param integer $deleted -- false if deleted records should not be include, true if deleted records should be included.
     * @return Array 'result_count' -- integer - The number of records returned
     *               'next_offset' -- integer - The start of the next page (This will always be the previous offset plus the number of rows returned.  It does not indicate if there is additional data unless you calculate that the next_offset happens to be closer than it should be.
     *               'entry_list' -- Array - The records that were retrieved
     *	     	     'relationship_list' -- Array - The records link field data. The example is if asked about accounts email address then return data would look like Array ( [0] => Array ( [name] => email_addresses [records] => Array ( [0] => Array ( [0] => Array ( [name] => id [value] => 3fb16797-8d90-0a94-ac12-490b63a6be67 ) [1] => Array ( [name] => email_address [value] => hr.kid.qa@example.com ) [2] => Array ( [name] => opt_out [value] => 0 ) [3] => Array ( [name] => primary_address [value] => 1 ) ) [1] => Array ( [0] => Array ( [name] => id [value] => 403f8da1-214b-6a88-9cef-490b63d43566 ) [1] => Array ( [name] => email_address [value] => kid.hr@example.name ) [2] => Array ( [name] => opt_out [value] => 0 ) [3] => Array ( [name] => primary_address [value] => 0 ) ) ) ) )
    * @exception 'SoapFault' -- The SOAP error, if any
    */
    Method [ <user, inherits="" sugarwebserviceimplv4,="" prototype="" sugarwebserviceimpl=""> public method get_entry_list ] {
      

      - Parameters [10] {
        Parameter #0 [  $session ]
        Parameter #1 [  $module_name ]
        Parameter #2 [  $query ]
        Parameter #3 [  $order_by ]
        Parameter #4 [  $offset ]
        Parameter #5 [  $select_fields ]
        Parameter #6 [  $link_name_to_fields_array ]
        Parameter #7 [  $max_results ]
        Parameter #8 [  $deleted ]
        Parameter #9 [  $favorites = false ]
      }
    }

/**

* Retrieve a list of beans. This is the primary method for getting list of SugarBeans from Sugar using the SOAP API.

* @param String $session -- Session ID returned by a previous call to login.

* @param String $module_name -- The name of the module to return records from. This name should be the name the module was developed under (changing a tab name is studio does not affect the name that should be passed into this method)..

* @param String $query -- SQL where clause without the word 'where'

* @param String $order_by -- SQL order by clause without the phrase 'order by'

* @param integer $offset -- The record offset to start from.

* @param Array $select_fields -- A list of the fields to be included in the results. <span style="color: #800000;"><strong>This optional parameter</strong></span> allows for only needed fields to be retrieved.

* @param Array $link_name_to_fields_array -- A list of link_names and for each link_name, what fields value to be returned. For ex.'link_name_to_fields_array' => array(array('name' => 'email_addresses', 'value' => array('id', 'email_address', 'opt_out', 'primary_address')))

* @param integer $max_results -- The maximum number of records to return. The default is the sugar configuration value for 'list_max_entries_per_page'

* @param integer $deleted -- false if deleted records should not be include, true if deleted records should be included.

* @return Array 'result_count' -- integer - The number of records returned

* 'next_offset' -- integer - The start of the next page (This will always be the previous offset plus the number of rows returned. It does not indicate if there is additional data unless you calculate that the next_offset happens to be closer than it should be.

* 'entry_list' -- Array - The records that were retrieved

* 'relationship_list' -- Array - The records link field data. The example is if asked about accounts email address then return data would look like Array ( [0] => Array ( [name] => email_addresses [records] => Array ( [0] => Array ( [0] => Array ( [name] => id [value] => 3fb16797-8d90-0a94-ac12-490b63a6be67 ) [1] => Array ( [name] => email_address [value] => hr.kid.qa@example.com ) [2] => Array ( [name] => opt_out [value] => 0 ) [3] => Array ( [name] => primary_address [value] => 1 ) ) [1] => Array ( [0] => Array ( [name] => id [value] => 403f8da1-214b-6a88-9cef-490b63d43566 ) [1] => Array ( [name] => email_address [value] => kid.hr@example.name ) [2] => Array ( [name] => opt_out [value] => 0 ) [3] => Array ( [name] => primary_address [value] => 0 ) ) ) ) )

* @exception 'SoapFault' -- The SOAP error, if any

Method [ <user, inherits="" sugarwebserviceimplv4,="" prototype="" sugarwebserviceimpl=""> public method get_entry_list ] {

- Parameters [10] {

Parameter #0 [ $session ]

Parameter #1 [ $module_name ]

Parameter #2 [ $query ]

Parameter #3 [ $order_by ]

Parameter #4 [ $offset ]

Parameter #5 [ $select_fields ]

Parameter #6 [ $link_name_to_fields_array ]

Parameter #7 [ $max_results ]

Parameter #8 [ $deleted ]

Parameter #9 [ $favorites = false ]

}

Looking at this it seems that the selected_fields parameter is optional which implies all the others are required. But one of the few examples I found showed the following:

        $parameters = array(
            'session' => $sessionId,
            'module_name' => 'Contacts',
            'query' => ''
        );    
        $json = json_encode($parameters);    
        $postArgs = array(
            'method' => 'get_entry_list',
            'input_type' => 'JSON',
            'response_type' => 'JSON',
            'rest_data' => $json
        );
        $response = sugar_http_request($sugarcrm_api_url, $postArgs); 
        $result = json_decode( $response ); 
    echo "

$parameters = array(

'session' => $sessionId,

'module_name' => 'Contacts',

'query' => ''

);

$json = json_encode($parameters);

$postArgs = array(

'method' => 'get_entry_list',

'input_type' => 'JSON',

'response_type' => 'JSON',

'rest_data' => $json

);

$response = sugar_http_request($sugarcrm_api_url, $postArgs);

$result = json_decode( $response );

echo "

no query

";
    print_r($result);

1 2	"; print_r($result);

Which worked fine, returning a data structure with all of the contacts. So I thought, great, lets try the same but with the selected_fields parameter set. So I changed it to:

      $parameters = array(
        'session' => $sessionId,
        'module_name' => 'Contacts',
        'query' => '',
        'select_fields' => array(
          'id',
          'first_name',
          'email1',
          'created_by_name'
          )
        );    
        $json = json_encode($parameters);    
        $postArgs = array(
            'method' => 'get_entry_list',
            'input_type' => 'JSON',
            'response_type' => 'JSON',
            'rest_data' => $json
        );
        $response = sugar_http_request($sugarcrm_api_url, $postArgs); 
        $result = json_decode( $response ); 
        print_r($result);

$parameters = array(

'session' => $sessionId,

'module_name' => 'Contacts',

'query' => '',

'select_fields' => array(

'id',

'first_name',

'email1',

'created_by_name'

)

);

$json = json_encode($parameters);

$postArgs = array(

'method' => 'get_entry_list',

'input_type' => 'JSON',

'response_type' => 'JSON',

'rest_data' => $json

);

$response = sugar_http_request($sugarcrm_api_url, $postArgs);

$result = json_decode( $response );

print_r($result);

This did not work! It still returned all fields. After much trial and error I added all parameters and actually got it to work.

        $parameters = array(
            'session' => $sessionId,
            'module_name' => 'Contacts',
            'query' => '',
            'order_by' => '',
            'offset' => '0',
            'select_fields' => array(
                'id',
                'first_name',
                'email1',
                'created_by_name'
     ),
     'link_name_to_fields_array' => array(
           
     ),
     //The maximum number of results to return.
     'max_results' => '12',
     //To exclude deleted records
     'deleted' => '0'
        );    
        $json = json_encode($parameters);    
        $postArgs = array(
            'method' => 'get_entry_list',
            'input_type' => 'JSON',
            'response_type' => 'JSON',
            'rest_data' => $json
        );
        $response = sugar_http_request($sugarcrm_api_url, $postArgs); 
        $result = json_decode( $response ); 
    print_r($result);

$parameters = array(

'session' => $sessionId,

'module_name' => 'Contacts',

'query' => '',

'order_by' => '',

'offset' => '0',

'select_fields' => array(

'id',

'first_name',

'email1',

'created_by_name'

'link_name_to_fields_array' => array(

//The maximum number of results to return.

'max_results' => '12',

//To exclude deleted records

'deleted' => '0'

);

$json = json_encode($parameters);

$postArgs = array(

'method' => 'get_entry_list',

'input_type' => 'JSON',

'response_type' => 'JSON',

'rest_data' => $json

);

$response = sugar_http_request($sugarcrm_api_url, $postArgs);

$result = json_decode( $response );

print_r($result);

This returned an array of data, below is an example of one of the records.

[3] => stdClass Object
(
   [id] => 994ba9b2-d998-d0cb-67ac-54a1f17f5391
   [module_name] => Contacts
   [name_value_list] => stdClass Object
    (
        [created_by_name] => stdClass Object
             (
                  [name] => created_by_name
                  [value] => Gail 
             )

             [id] => stdClass Object
             (
                  [name] => id
                  [value] => 994ba9b2-d998-d0cb-67ac-54a1f17f5391
              )

             [first_name] => stdClass Object
             (
                  [name] => first_name
                  [value] => kelly
             )

             [email1] => stdClass Object
             (
                  [name] => email1
                  [value] => kelly@*******.org
              )
    )

)

[3] => stdClass Object

(

[id] => 994ba9b2-d998-d0cb-67ac-54a1f17f5391

[module_name] => Contacts

[name_value_list] => stdClass Object

(

[created_by_name] => stdClass Object

(

[name] => created_by_name

[value] => Gail

)

[id] => stdClass Object

(

[name] => id

[value] => 994ba9b2-d998-d0cb-67ac-54a1f17f5391

)

[first_name] => stdClass Object

(

[name] => first_name

[value] => kelly

)

[email1] => stdClass Object

(

[name] => email1

[value] => kelly@*******.org

)

So now on to writing a query parameter to search by e-mail address. The REST API wants a syntactically correct SQL “where” statement, just without the “where keyword.” So looking at the data that was returned you would expect to be able to query on the field “email1.” Don’t bother trying. Take a look at the forum discussion at: http://forums.sugarcrm.com/f3/mysterious-email-field-contacts-58318/.

It turns out that email1 is a special “reference point,” not an actual field. So to be able to write correct “where” statements you pretty much need to look at the database itself. I opened phpMyAdmin and took a look at the contacts table. Examining the fields shows nothing relating to email addresses. But there are other email tables, so you will have to create a where statement using database table joins.

Here is where I got lucky. I did a search and found one example showing this query.

 'query' => "contacts.id in (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 and ea.email_address LIKE '".$contact_email."{a5bdf8d0011d37eacb4818f08a39964e49b498fda098284cc705f141d11fed72}')",

1	'query' => "contacts.id in (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 and ea.email_address LIKE '".$contact_email."{a5bdf8d0011d37eacb4818f08a39964e49b498fda098284cc705f141d11fed72}')",

Of course there was no description of what actually is going on with the query. So looking at the tables in the query, there are two. They are email_addresses and email_addr_bean_rel. email_addresses is pretty straight forward. It contains fields for the email address, email address in all caps, and flags for opt_out, invalid_email, and deleted.

email_addr_bean_rel is a mapping table that links the email address to records in other tables, such as contacts, leads, users etc.

So using phpMyAdmin I converted the php query into actual SQL statements so I could see what was going on. I created an SQL query to match the php query.

SELECT *
FROM contacts
WHERE contacts.id
IN (
SELECT eabr.bean_id
FROM email_addr_bean_rel eabr
JOIN email_addresses ea ON ( ea.id = eabr.email_address_id )
WHERE eabr.deleted =0
AND ea.email_address LIKE 'test@hbdesign.com'
)

SELECT *

FROM contacts

WHERE contacts.id

IN (

SELECT eabr.bean_id

FROM email_addr_bean_rel eabr

JOIN email_addresses ea ON ( ea.id = eabr.email_address_id )

WHERE eabr.deleted =0

AND ea.email_address LIKE 'test@hbdesign.com'

)

This query worked and took about .05 seconds to run. Looking at the sub query I noticed that it was returning all records from the relation table regardless of which module was being related. The relationship table has fields for the bean_module (i.e., contacts, leads, etc.). So if the email address being looked for was related to multiple modules, then the sub query would return multiple records. I wondered if it would be more efficient for the sub query to return only those records in the contacts module. So I modified the query to be:

SELECT *
FROM contacts
WHERE contacts.id
IN (
SELECT eabr.bean_id
FROM email_addr_bean_rel eabr
JOIN email_addresses ea ON ( ea.id = eabr.email_address_id )
WHERE eabr.deleted =0
AND ea.email_address LIKE 'test@hbdesign.com'
and eabr.beans_module = 'contacts'
)

SELECT *

FROM contacts

WHERE contacts.id

IN (

SELECT eabr.bean_id

FROM email_addr_bean_rel eabr

JOIN email_addresses ea ON ( ea.id = eabr.email_address_id )

WHERE eabr.deleted =0

AND ea.email_address LIKE 'test@hbdesign.com'

and eabr.beans_module = 'contacts'

)

The query took about the same amount of time, but my data set is small. It might make a difference with a larger data set.

So below is the final code fragment used to return the contact based on searching for the email address:

    $parameters = array(
	        'session' => $sessionId,
		'module_name' => 'Contacts',
                'query' => "contacts.id in (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 and and eabr.bean_module like 'contacts' and ea.email_address LIKE '".$contact_email."{a5bdf8d0011d37eacb4818f08a39964e49b498fda098284cc705f141d11fed72}')",     
      'order_by' => '',
      'offset' => '0',
      'select_fields' => array(
          'id',
          'first_name',
          'email1',
          'created_by_name'
     ),
     'link_name_to_fields_array' => array(
     ),

     //The maximum number of results to return.
     'max_results' => '',
     //To exclude deleted records
     'deleted' => '0'
		);	
		$json = json_encode($parameters);	
		$postArgs = array(
			'method' => 'get_entry_list',
			'input_type' => 'JSON',
			'response_type' => 'JSON',
			'rest_data' => $json
		);
		$response = sugar_http_request($sugarcrm_api_url, $postArgs); 
		$result = json_decode( $response ); 
    print_r($result);

$parameters = array(

'session' => $sessionId,

'module_name' => 'Contacts',

'query' => "contacts.id in (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 and and eabr.bean_module like 'contacts' and ea.email_address LIKE '".$contact_email."{a5bdf8d0011d37eacb4818f08a39964e49b498fda098284cc705f141d11fed72}')",

'order_by' => '',

'offset' => '0',

'select_fields' => array(

'id',

'first_name',

'email1',

'created_by_name'

'link_name_to_fields_array' => array(

//The maximum number of results to return.

'max_results' => '',

//To exclude deleted records

'deleted' => '0'

);

$json = json_encode($parameters);

$postArgs = array(

'method' => 'get_entry_list',

'input_type' => 'JSON',

'response_type' => 'JSON',

'rest_data' => $json

);

$response = sugar_http_request($sugarcrm_api_url, $postArgs);

$result = json_decode( $response );

print_r($result);

Lessons learned

There are not a lot of good examples of using REST calls to interface with Sugar CE. The basic concepts of using the REST interface are pretty easy, but you need to understand the underlying data base structure to make good queries. And for anything complicated you should have a firm grasp of SQL, especially joins.

It would be nice if Sugar improved their documentation. I would suggest that they look at the WordPress codex and at the PHP documentations on the web. I especially like that the PHP documentation includes user submitted code.

So if you are trying to write REST code to do things with your Sugar CE data, best wishes. I hope this helped. Below are a few of the links that I found helpful:

Contacts from SugarCRM CE by email address

no query

Lessons learned

Categories