HOWTO: Search for a record via email address via Web Services

bsoremsugar —  November 12, 2010 — 9 Comments

Even though we just started a series on using SugarCRM web services in various different languages, I thought I would take a quick aside to bring up a common issue I hear about quite a bit in the forums.

Today one of our advanced support engineers asked how to search by email address for a record in a module. Fortunately, someone pointed us towards a post in the forums that details a good workaround for this issue. The problem that plagues the email fields in modules is that we store email address in a different set of tables than the rest of the module data, so you need to correctly join against that table in the SOAP call.

Below is a complete end-to-end solution that connects to the web services, creates a contact record, and then attempts to see if we can find the newly created record. I’m using the PHP native SoapClient for this example.

$soapClient = new SoapClient("http://localhost/mango/build/rome/builds/ent/sugarcrm/service/v3_1/soap.php?wsdl");

// Login
$user_name = 'admin';
$user_password = 'sugar';

try {
    $info = $soapClient->login(
        array(
            'user_name' => $user_name,
            'password'  => md5($user_password),
            )
        );
}
catch (SoapFault $fault) {
    die("Sorry, the service returned the following ERROR: ".$fault->faultcode."-".$fault->faultstring.".");
}

$session = $info->id;
echo "Successful Login! Session ID {$session}n";

// Create Contact
try {
    $info = $soapClient->set_entry(
        $session,
        'Contacts',
        array(
            array('name' => 'first_name', 'value' => 'Foo'),
            array('name' => 'last_name', 'value' => 'Bar'),
            array('name' => 'description', 'value' => 'This is a contact created from a REST web services call'),
            array('name' => 'email1', 'value' => 'foo@bar.com'),
            )
        );
}
catch (SoapFault $fault) {
    die("Sorry, the service returned the following ERROR: ".$fault->faultcode."-".$fault->faultstring.".");
}

$contactId = $info->id;
echo "Created Contact ID {$contactId}n";

// Now let's try to find the contact we just created by e-mail address
try {
    $info = $soapClient->get_entry_list(
        $session,
        'Contacts',
        "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 = 'foo@bar.com')",
        '',
        0,
        array(),
        10,
        -1
        );
}
catch (SoapFault $fault) {
    die("Sorry, the service returned the following ERROR: ".$fault->faultcode."-".$fault->faultstring.".");
}

$found = false;
foreach ( $info->entry_list as $entry ) {
    if ( $entry->id == $contactId ) {
        echo "Found Contact {$entry->id} by searching by emailn";
        $found = true;
        break;
    }
}
if ( !$found ) {
    die("Failure: Could not find the Contact {$contactId} by searching by email");
}

The most import part is in the get_entry_list() call, where we can specify the SQL query to use of the lookup. That query gets put into the WHERE clause of the query as it is run, which is just what we need to lookup the contact by email address.

9 responses to HOWTO: Search for a record via email address via Web Services

  1. 

    The included query has TERRIBLE performance in SugarCRM 5!

    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 = ‘foo@bar.com’)”,

    Using it for a sync connector with batches of even 100 would result in so many timeouts. Why don’t you tell developers to use

    email = ‘foo@bar.com’

    which is much, much faster?

    • 

      I don’t think that will work, since email is not a valid field in the contacts table.

      You could use email1, but that field is only present on older modules and won’t catch cases where the email address you are looking for isn’t the primary one.

  2. 

    This does seem to be the only way to find contacts by email over the SOAP and REST APIs (up to SugarCRM 6.5 at least), but as the number of contacts grows, performance becomes appalling using MySQL. The trouble is, MySQL does not optimise sub-queries at all – it executes them for each outer row retrieved, even when we know the sub-select is going to retrieve the same results every single time. Oracle has this figured out nearly 20 years ago, but MySQL has yet to catch up.

    You can follow relations from contacts to email addresses, and fetch all email addresses for contacts that you match. What does not work is following the relation in the opposite direction; the relation from email addresses to contacts returns zero matches every single time, which is a great shame, because doing what this article recommends – the only solution available over the *standard* SOAP or REST interfaces – kills performance of larger systems. If the relation could be followed, then it would help no-end.

    The Opacus email archiving plugin also does this (badly) and hammers the database horribly.

  3. 

    Whats the best way to retrieve a list of records by email address in rest/v10 API?

    Was hoping something like this would work:

    GET/Contacts

    {
    “filter”:[
    {
    "email1":"john@providentcrm.com"
    }
    ]
    }

    However this is what I get back:(

    {
    “error”: “invalid_parameter”,
    “error_message”: “Can not use invalid field email1 in condition.”
    }

    Any Ideas….searching for records with a particular email address should not be hard and it needs to be performant for large record sets.

  4. 

    The response looks like this:

    {
    “next_offset”: -1,
    “records”: [
    {
    “id”: “23fe316c-5a1e-2465-72c0-53ba5bf2a818″,
    “name”: “Willis Colletti”,
    “date_modified”: “2014-07-07T09:31:34+01:00″,
    “description”: “”,
    “email”: [
    {
    "email_address": "kid.phone.hr@example.tw",
    "primary_address": true,
    "reply_to_address": false,
    "invalid_email": false,
    "opt_out": false
    },
    {
    "email_address": "phone.kid@example.tv",
    "primary_address": false,
    "reply_to_address": false,
    "invalid_email": false,
    "opt_out": true
    }
    ],
    “_acl”: {
    “fields”: {}
    },
    “_module”: “Contacts”
    }
    ]
    }

Trackbacks and Pingbacks:

  1. contact/account by email via REST - October 25, 2012

    [...] [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s