SugarCRM SupportProduct GuidesSugar DeveloperSugar Developer Guide 12.0Data FrameworkDatabaseSugarQuery

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:

  • alias - string - The alias for the module table in the generated SQL query
  • team_securityboolean - Whether or not Team Security should be added to the generated SQL query 
  • add_deletedboolean - Whether or not 'deleted' = 0 should be added to Where clause of generated SQL query

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:

  • alias - string - The alias for the module table in the generated SQL query
  • relatedJoinstring - If joining to a secondary table (related to a related module), such as joining on Opportunities related to Contacts, when querying from Accounts, you can specify either the name of the relationship or the alias you specified for that relationship table.

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:

  • alias - string - The alias for the module table in the generated SQL query

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:

  • db - Returns the result directly from the DatabaseManager resource
  • array Default - Returns the results as a formatted array
  • json - Returns the results encoded as JSON

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.

Topics

    Learn about the various methods that can be utilized with SugarQuery to add conditional statements to a query.
    Learn about some of the advanced methods that SugarQuery has to offer, that are not as commonly used.

Last modified: 2022-02-03 20:25:09