Advanced Techniques
Overview
Learn about some of the advanced methods that SugarQuery has to offer, that are not as commonly used.
Get First Record
Getting the first record in a result set, can be accomplished by using the limit()
method. The getOne()
method is similar in that it gets the first record, but it also returns the first piece of data for that record.
getOne()
Get the first piece of data on the first record returned by the generated query. In this example, we want the 'name' from the Account with a given ID.
$SugarQuery = new SugarQuery();
$SugarQuery->select(array('name'));
$SugarQuery->from(BeanFactory::newBean('Accounts'));
$SugarQuery->where()->equals('id',$id);
//Get the Name of the account
$accountName = $SugarQuery->getOne();
Aggregates
setCountQuery()
Currently, the only method available for creating an aggregate column, is the setCountQuery()
method on the SugarQuery_Builder_Select Object. You can add this method to your select()
method chain, to add count(0) as record_count
to the SQL SELECT statement.
$SugarQuery = new SugarQuery();
$SugarQuery->select(array('name'))->setCountQuery();
$SugarQuery->from(BeanFactory::newBean('Accounts'));
$SugarQuery->groupByRaw('accounts.name');
The above example will output the following prepared statement when using compile():
SELECT accounts.name, COUNT(0) AS record_count FROM accounts WHERE accounts.deleted = ? GROUP BY accounts.name, accounts.name
Parameters
array (
[1] => 0
)
Arguments
No arguments
Returns
SugarQuery_Builder_Select Object
Allows for method chaining on the Select Object.
Joins
Joining to tables and joining via SugarBean relationships is outlined in the SugarQuery documentation, however the SugarQuery_Builder_Join
Object has a few helpful methods not mentioned there.
joinName()
If you are not using a custom alias for the relationship or table, you may want to retrieve the generated name used by SugarQuery to add a conditions or join to.
$SugarQuery = new SugarQuery();
$SugarQuery->from(BeanFactory::getBean('Accounts'));
$contacts = $SugarQuery->join('contacts')->joinName();
$SugarQuery->select(array("$contacts.full_name"));
$SugarQuery->where()->equals('industry', 'Media');
The above example will output the following prepared statement when using compile():
SELECT jt0_contacts.salutation rel_full_name_salutation, jt0_contacts.first_name rel_full_name_first_name, jt0_contacts.last_name rel_full_name_last_name FROM accounts INNER JOIN accounts_contacts jt1_accounts_contacts ON (accounts.id = jt1_accounts_contacts.account_id) AND (jt1_accounts_contacts.deleted = ?) INNER JOIN contacts jt0_contacts ON (jt0_contacts.id = jt1_accounts_contacts.contact_id) AND (jt0_contacts.deleted = ?) WHERE (accounts.industry = ?) AND (accounts.deleted = ?)
Parameters:
array (
[1] => 0
[2] => 0
[3] => Media
[4] => 0
)
Arguments
No arguments
Returns
string
The name used in Query to identify the joined table
Unions
Unions allow joining multiple queries with the same selected fields to be combined during output. You can use Unions in SugarQuery by using the union()
method.
union()
To add a union, you can use the corresponding union()
method. The example below will join two SQL queries:
//Fetch the bean of the module to query
$bean = BeanFactory::newBean('Accounts');
//Specify fields to fetch
$fields = array(
'id',
'name'
);
//Create first query
$sq1 = new SugarQuery();
$sq1->select($fields);
$sq1->from($bean, array('team_security' => false));
$sq1->Where()->in('account_type', array('Customer'));
//Create second query
$sq2 = new SugarQuery();
$sq2->select($fields);
$sq2->from($bean, array('team_security' => false));
$sq2->Where()->in('account_type', array('Investor'));
//Create union
$sqUnion = new SugarQuery();
$sqUnion->union($sq1);
$sqUnion->union($sq2);
$sqUnion->limit(5);
The above example will output the following prepared statement when using compile():
SELECT accounts.id, accounts.name FROM accounts WHERE (accounts.account_type IN (?)) AND (accounts.deleted = ?) UNION ALL SELECT accounts.id, accounts.name FROM accounts WHERE (accounts.account_type IN (?)) AND (accounts.deleted = ?) LIMIT 5
Parameters:
array (
[1] => Customer
[2] => 0
[3] => Investor
[4] => 0
)
Arguments
Name | Type | Required | Description |
$select | SugarQuery | true | The SugarQuery Object you wish to add to the UNION query |
$all | Boolean | false | Whether to use UNION ALL or just UNION in the query. The default value is TRUE. |
Returns
SugarQuery_Builder_Union Object
Allows for method chaining on the Union Object.
Having
When using aggregates in a query, you might want to filter out values based on a condition. SugarQuery provides the having()
method for adding HAVING clause to the query.
having()
To use the having()
method, you have to build a SugarQuery_Builder_Condition
Object and set the field, operator, and value that condition is based on.
$SugarQuery = new SugarQuery();
$SugarQuery->from(BeanFactory::getBean('Accounts'));
$SugarQuery->join('contacts', array('alias' => 'industryContacts'));
$SugarQuery->join('opportunities', array('relatedJoin' => 'industryContacts', 'alias' => 'contactsOpportunities'));
$SugarQuery->select()->setCountQuery();
$SugarQuery->where()->equals('contactsOpportunities.sales_stage', 'closed');
$havingCondition = new SugarQuery_Builder_Condition($SugarQuery);
$havingCondition->setField('contactsOpportunities.amount')->setOperator('>')->setValues('1000');
$SugarQuery->having($havingCondition);
The above example will output the following prepared statement when using compile():
SELECT COUNT(0) AS record_count FROM accounts INNER JOIN accounts_contacts jt0_accounts_contacts ON (accounts.id = jt0_accounts_contacts.account_id) AND (jt0_accounts_contacts.deleted = ?) INNER JOIN contacts industryContacts ON (industryContacts.id = jt0_accounts_contacts.contact_id) AND (industryContacts.deleted = ?) INNER JOIN opportunities_contacts jt1_opportunities_contacts ON jt1_opportunities_contacts.deleted = ? INNER JOIN opportunities contactsOpportunities ON (contactsOpportunities.id = jt1_opportunities_contacts.opportunity_id) AND (contactsOpportunities.deleted = ?) WHERE (contactsOpportunities.sales_stage = ?) AND (accounts.deleted = ?) HAVING contactsOpportunities.amount > ?
Parameters:
array (
[1] => 0
[2] => 0
[3] => 0
[4] => 0
[5] => closed
[6] => 0
[7] => 1000
)
Arguments
Name | Type | Required | Description |
$condition | SugarQuery_Builder_Condition | true | The conditional object used to generate the HAVING clause |
Returns
SugarQuery_Builder_Having Object
Allows for method chaining on the Having Object to add additional conditions.
Raw Methods
The SugarQuery Object has a few helper methods that allow raw SQL statement parts to be passed into. This allows for more complex statements or edge case scenarios where a helper function may not have met the requirements for the query.
whereRaw()
To add to the WHERE clause of SugarQuery Object with raw SQL syntax, you can utilize the whereRaw()
method. This method will append the specified statement, to the WHERE clause using an AND operator, and will wrap the entire statement in parenthesis. The following is an example use with the output:
$SugarQuery = new SugarQuery();
$SugarQuery->select(array('name'));
$SugarQuery->from(BeanFactory::newBean('Accounts'));
$SugarQuery->whereRaw("name LIKE '%T%'");
The above example will output the following prepared statement when using compile():
SELECT accounts.name FROM accounts WHERE (name LIKE '%T%') AND (accounts.deleted = ?)
Parameters:
array (
[1] => 0
)
Arguments
Name | Type | Required | Description |
$sql | String | true | The WHERE clause SQL to be appended to the where clause on the SugarQuery object. All conditions passed in are wrapped in parenthesis and appended using AND (if other conditions exist on where clause). |
Returns
SugarQuery_Builder_Where Object
Allows for method chaining on the Where object.
groupByRaw()
To add multiple fields to the GROUP BY statement on the SugarQuery Object, it may be easiest to use the groupByRaw() method.
$SugarQuery = new SugarQuery();
$SugarQuery->select(array('account_type', 'industry'));
$SugarQuery->from(BeanFactory::newBean('Accounts'));
$SugarQuery->groupByRaw("accounts.account_type,accounts.industry");
The above example will output the following prepared statement when using compile():
SELECT accounts.account_type, accounts.industry FROM accounts WHERE accounts.deleted = ? GROUP BY accounts.account_type,accounts.industry
Parameters:
array (
[1] => 0
)
Arguments
Name | Type | Required | Description |
$sql | String | true | The GROUP BY statement, without the GROUP BY keyword. |
Returns
SugarQuery Object
Allows for method chaining on the SugarQuery Object.
orderByRaw()
Using the oderBy()
method only allows for adding a single field to the SugarQuery object at a time. In some cases, you might consider using the orderByRaw()
method to add multiple fields or the entire ORDER BY statement to the SugarQuery object.
$SugarQuery = new SugarQuery();
$SugarQuery->select(array('name'));
$SugarQuery->from(BeanFactory::newBean('Accounts'));
$SugarQuery->orderByRaw("accounts.name DESC, accounts.date_modified");
The above example will output the following prepared statement when using compile():
SELECT accounts.name FROM accounts WHERE accounts.deleted = ? ORDER BY accounts.name DESC, accounts.date_modified DESC, accounts.id DESC
Parameters:
array (
[1] => 0
)
Arguments
Name | Type | Required | Description |
$sql | String | true | The ORDER BY statement, without the ORDER BY keyword. |
Returns
SugarQuery Object
Allows for method chaining on the SugarQuery Object.
havingRaw()
Using the havingRaw()
method allows for adding a having statement to the SugarQuery object.
$SugarQuery = new SugarQuery();
$SugarQuery->from(BeanFactory::getBean('Accounts'));
$SugarQuery->join('contacts', array('alias' => 'industryContacts'));
$SugarQuery->join('opportunities', array('relatedJoin' => 'industryContacts', 'alias' => 'contactsOpportunities'));
$SugarQuery->select()->setCountQuery();
$SugarQuery->where()->equals('contactsOpportunities.sales_stage', 'closed');
$SugarQuery->havingRaw("contactsOpportunities.amount > 1000");
The above example will output the following prepared statement when using compile():
SELECT COUNT(0) AS record_count FROM accounts INNER JOIN accounts_contacts jt0_accounts_contacts ON (accounts.id = jt0_accounts_contacts.account_id) AND (jt0_accounts_contacts.deleted = ?) INNER JOIN contacts industryContacts ON (industryContacts.id = jt0_accounts_contacts.contact_id) AND (industryContacts.deleted = ?) INNER JOIN opportunities_contacts jt1_opportunities_contacts ON jt1_opportunities_contacts.deleted = ? INNER JOIN opportunities contactsOpportunities ON (contactsOpportunities.id = jt1_opportunities_contacts.opportunity_id) AND (contactsOpportunities.deleted = ?) WHERE (contactsOpportunities.sales_stage = ?) AND (accounts.deleted = ?) HAVING contactsOpportunities.amount > 1000
Parameters:
array (
[1] => 0
[2] => 0
[3] => 0
[4] => 0
[5] => closed
[6] => 0
)
Arguments
Name | Type | Required | Description |
$sql | String | true | The HAVING statement, without the HAVING keyword. |
Returns
SugarQuery Object
Allows for method chaining on the SugarQuery Object.