SugarCRM API Search & Query Capabilities

By Brad Scharmann in Developer Posted Jun 25, 2015

When it came time to build Cloud Elements’ integration with the SugarCRM API, we were pleased to find that their recently released v10 API offered RESTful endpoints and their resources matched our CRM hub resources almost 1 to 1. 

Unfortunately, one thing we did find difficult to implement was search capabilities, or filters as they are called in the SugarCRM system. It is here that Cloud Elements provides an incredible value-add by simplifying this process and allowing our customers to use CEQL (Cloud Elements Query Language), a SQL like query language we implement across our services. Let’s take a look at some examples to understand more.

sugarcrm-api-search-banner-1920x540-2Before we begin we must first mention that we were greatly aided by the logical breakdown in this article from SugarCRM’s own developer site. It allowed us to understand the structure of their filters and map them to CEQL. If you are interested in reading more about how SugarCRM API filters are implemented, we highly suggest giving the article a read. For more information on Cloud Elements Query Language, see our usage guide here.

Our first example will begin with a simple query to visualize how both SugarCRM API and Cloud Elements break it down. Consider the following JSON object:

{ 
"name": "Brad Scharmann", 
"twitter": "@bscharm", 
"first_name": "Brad", 
"last_name": "Scharmann", 
“date_entered”: “2015-05-01” 
}

Both Cloud Elements and SugarCRM use query parameters for searching/filtering. We can see the differences between the two here, preceded by the “corresponding” SQL query:

SELECT * FROM {object} WHERE name='Brad';
/{object}?where=name='Brad'
/{object}?filter[0][name][$equals]='Brad'

Cloud Elements uses the “where” query parameter similar to the where clause in a normal SQL query. SugarCRM uses a somewhat complex array structure to organize filters, which must then be translated to query parameters. For this trivial example there is not much work to do on the Sugar side, as there is only 1 entry into the array. To show where things get a bit more difficult to translate we will look at a more compound query with several conditions. Assuming we are working with the same JSON object, let’s again look at a SQL query, a Cloud Elements CEQL query and the corresponding SugarCRM query.

SELECT * FROM {object} WHERE (last_name='Scharmann' AND (name='Brad Scharmann' OR name='Foo Bar')) AND (date_entered='2015-05-01' AND twitter='@bscharm');

/{object}?where=(last_name='Scharmann' AND (name='Brad Scharmann' OR name='Foo Bar')) AND (date_entered='2015-05-01' AND twitter='@bscharm')

/{object}?filter[0][$and][0][$and][0][last_name][$equals]='Scharmann'&filter[0][$and][0][$and][1][$or][0][name][$equals]='Brad Scharmann'&filter[0][$and][0][$and][1][$or][1][name][$equals]='Foo Bar'&filter[0][$and][1][$and][0][date_entered][$equals]='2015-05-01'&filter[0][$and][1][$and][1][twitter][$equals]='@bscharm'

As you can see in this example, the SQL query and CEQL query are nearly identical. The SugarCRM query, while logically sound, is difficult to reason about and quite verbose at the end of the day. Constructing the array structure necessary to organize the filters and then breaking it into query parameters proved a significant task. By abstracting away this process and allowing our customers to use our own query language, we are aiming to simplify the integration between you and SugarCRM, one of the leading CRM platforms available today.

10 Step Guide to API Integrations