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

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

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

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

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

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

Returns

SugarQuery Object

Allows for method chaining on the SugarQuery Object.