SugarQuery

Overview

SugarQuery, located in ./include/SugarQuery/SugarQuery.php, provides an object-oriented approach to working with the database. This allows developers to generate the applicable SQL for a Sugar system without having to know which database backend the instance is using. SugarQuery supports all databases supported by Sugar.

Note: SugarQuery only supports reading data from the database at this time (i.e. SELECT statements). 

Setup

To use SugarQuery, simply create a new SugarQuery object.

$sugarQuery = new SugarQuery();

Basic Usage

Using the SugarQuery object to retrieve records or generate SQL queries is very simple. At a minimum you need to set the Module you are working with, using the from() method, however, there are helper methods for just about any operation you would need in a SQL query. The methods listed below will outline the major methods you should consider utilizing on the SugarQuery object in order to achieve your development goals.

from()

The from() method is used to set the primary module the SugarQuery object will be querying from. It is also used to set some crucial options for the query, such as whether Team Security should be used or if only non-deleted records should be queried. The following example will set the SugarQuery object to query from the Accounts module.

$sugarQuery->from(BeanFactory::newBean('Accounts'));

Arguments

Returns

SugarQuery Object

Allows for method chaining on the SugarQuery object.

select()

The example above demonstrates the most basic example of retrieving records from a module. The select() method can be used on the SugarQuery object to specify the specific fields you wish to retrieve from the query.

//Alter the Selected Fields
$sugarQuery->select(array('id', 'name'));

Arguments

Returns

SugarQuery_Builder_Select Object

You cannot chain SugarQuery methods off of the select() method, however, you can use the returned Select object to modify the SELECT portion of the statement. Review the SugarQuery_Builder_Select object in ./include/SugarQuery/Builder/Select.php for additional information on usage.

where()

To add a WHERE clause to the query, use the where() method to generate the Where object, and then use method chaining with the various helper methods to add conditions. To add a WHERE clause for records with the name field containing the letter "I", you could add the following code. 

//add the where clause
$sugarQuery->where()->contains('name', 'I');

Arguments

None

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where object as shown above. Review the SugarQuery Conditions documentation for a full spectrum of where() method usage.

Relationships

join()

To add data from a related module to the SugarQuery, use the join() method. Adding to the same SugarQuery code example in this page, the following code would add the JOIN from Accounts module tables to Contacts table:

//add join
$sugarQuery->join('contacts');

Arguments

Returns

SugarQuery_Builder_Join Object

Allows for method chaining on the SugarQuery_Builder_Join Object, to add additional conditions to the WHERE clause of the SQL condition. 

joinTable()

If you were using the joinRaw() method in previous versions of Sugar, this is the replacement method which allows for joining to a related table in SugarQuery. Adding to the same SugarQuery code example in this page, the following code would add the JOIN from Accounts module tables to the accounts_contacts table:

//add join
$sugarQuery->joinTable('accounts_contacts', array('alias' => 'ac'))->on()
    ->equalsField('accounts.id','ac.account_id')
    ->equals('ac.primary_account',1);

Arguments

Returns

SugarQuery_Builder_Join Object

Allows for method chaining on the SugarQuery_Builder_Join Object, to add additional conditions to the ON clause using the on() method. 

Altering Results

Altering the result set of a query can help the performance, as well as be crucial to finding the correct data. The following methods provide ways to limit the result set and change the order.

distinct()

To group the query on a field, you can use the corresponding distinct() method.

//add group by
$sugarQuery->distinct(true);

Arguments

Returns

Current SugarQuery Object

Allows for method chaining on the SugarQuery Object.

limit()

To limit the results of the query, you can use the limit() method. 

//set the limit 
$sugarQuery->limit(10);

Arguments

Returns

Current SugarQuery Object

Allows for method chaining on the SugarQuery Object.

offset()

Adding a limit to the query limits the rows returned, however when doing so, you may need to alter the offset of the query to account for pagination or access other portions of the result set. To set an offset, you can use the offset() method.

//set the offset
$sugarQuery->offset(5);

Arguments

Returns

Current SugarQuery Object

Allows for method chaining on the SugarQuery Object.

orderBy()

To order the query on a field, you can use the corresponding orderBy() method. This method can be called multiple times, to add multiple fields to the order by clause of the query.

//add group by
$sugarQuery->orderBy('account_type');

Arguments

Returns

Current SugarQuery Object

Allows for method chaining on the SugarQuery Object.

Execution

Once you have the SugarQuery object setup and configured for your statement, you will want to retrieve the results of the query, or simply get the generated query for the object. The following methods are used for executing the SugarQuery object.

execute()

To query the database for a result set, you will use the execute() method. The execute() method will retrieve the results and return them as a raw string, db object, json, or an array depending on the $type parameter. By default, results are returned as an array. An example of fetching records from an account is below:

//fetch the result
$result = $sugarQuery->execute();

The execute() function will return an array of results that you can iterate through as shown below:

Array
(
    [0] => Array
    (
        [id] => f39593da-3f88-3059-4f18-524b4d23d07b
        [name] => International Art Inc
    )
)

Note: An empty resultset will return an empty array.

Arguments

Returns

Default: Array. See above argument details for details on other Return options.

compile()

If you want to log the query being generated or want to output the query without running it during development, the compile() method is what should be used retrieve the Prepared Statement. You can then retrieve the Prepared Statement Object to retrieve the Parameterized SQL and the Parameters. For further information on Prepared Statement usage, see our Database documentation.

//get the compiled prepared statement
$preparedStmt = $sugarQuery->compile();

//Retrieve the Parameterized SQL
$sql = $preparedStmt->getSQL();

//Retrieve the parameters as an array
$parameters = $preparedStmt->getParameters();

Arguments

No arguments

Returns

Object

The compiled SQL Query built by the SugarQuery object.