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
Name | Type | Required | Description |
$bean | SugarBean Object | true | The SugarBean object for a specified module. The SugarBean object does not have to be a blank or new Bean as seen in the example above, but can be a previously instantiated SugarBean object. |
$options | Array | false |
An associative array that can specify any of the following options:
|
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
Name | Type | Required | Description |
$fields | Array | false | Sets the fields that should be added to the SELECT portion of the SQL query |
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
Name | Type | Required | Description |
$link_name | String | true | The name of the relationship |
$options | Array | false |
An associative array that can specify any of the following options:
|
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
Name | Type | Required | Description |
$table_name | String | true | The name of the database table to join. |
$options | Array | false |
An associative array that can specify any of the following options:
|
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
Name | Type | Required | Description |
$value | Boolean | true | Set whether or not the DISTINCT statement should be added to the query |
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
Name | Type | Required | Description |
$number | Integer | true | The max amount of rows that should be returned by the query |
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
Name | Type | Required | Description |
$number | Integer | true | The offset amount of rows, or starting point, of the result |
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
Name | Type | Required | Description |
$column | String | true | The field you want the query to be grouped on |
$direction | String | false | Sets the direction of sorting. Must be 'ASC' or 'DESC'. The default is 'DESC'. |
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
Name | Type | Required | Description |
$type | String | false |
How you want the results of the Query returned. Can be one of the following options:
|
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.