SugarQuery Conditions

Overview

Learn about the various methods that can be utilized with SugarQuery to add conditional statements to a query. 

Where Clause

Manipulating, the WHERE clause of a SugarQuery object is crucial for getting the correct results. To create a WHERE clause on the query, use the where() method on the SugarQuery object, as outlined in the SugarQuery documentation. Once you have the Where object, you can utilize the following methods on the Where object to add conditional statements.

equals() | notEquals()

Used to equate a field to a given value. Wildcards will not work with this function, as it is looking for an exact match.

//add equals
$SugarQuery->where()->equals('name','Test');

//add Not Equals
$SugarQuery->where()->notEquals('name','Tester');

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where object to add additional conditions.

equalsField() | notEqualsField()

Used to equate a field to another field in the result set.

//add an Equals Field statement
$SugarQuery->where()->equalsField('industry','account_type');

//add a Not Equals Field statement
$SugarQuery->where()->notEqualsField('name','account_type');

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where object to add additional conditions. 

isEmpty() | isNotEmpty()

Used to check if a field is or isn't empty.

//add an isEmpty statement 
$SugarQuery->where()->isEmpty('industry'); 

//add an isNotEmpty statement 
$SugarQuery->where()->isNotEmpty('name');

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where object to add additional conditions. 

isNull() | notNull()

Used to check if a field is or isn't equal to NULL.

//add an isNull statement 
$SugarQuery->where()->isNull('industry');
 
//add a notNull statement 
$SugarQuery->where()->notNull('name');

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where object to add additional conditions. 

contains() | notContains()

Used to check if a field has or doesn't have a specified string in its value. Utilizes the LIKE statement, and wildcards on both sides of the provided string.

//add an isNull statement 
$SugarQuery->where()->contains('name','Test');

//add a notNull statement 
$SugarQuery->where()->notContains('industry','Test');

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where Object to add additional conditions. 

starts() | ends()

Similar to the above contains() method, these methods use the LIKE statement in the SQL query and wildcards for searching for a specified string in the field's value. However, the starts() and ends() methods only wildcard the right side and the left side, respectively. The following example demonstrates searching for records where the Name field starts with A, and ends with E.

//add an starts and ends statement 
$SugarQuery->where()->starts('name','A')->ends('name','e');

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where object to add additional conditions. 

in() | notIn()

Used to check if a field's value is or isn't one of a set of specified values. The following examples look for records where the industry field is in a list of values, and not in a separate list of values.

$values = array(
    'Support',
    'Sales',
    'Engineering'
);

//add in statement
$SugarQuery->where()->in('industry',$values);

$values = array(
    'Marketing',
    'Accounting'
);

//add NotIn Statement
$SugarQuery->where()->notIn('industry',$values);

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where object to add additional conditions. 

between()

Used primarily for numeric type fields, to check if the value is greater than the minimum number specified and less than the maximum number specified. The following code would check for records where the employees field is between 50 and 1000.

//add Between statement
$SugarQuery->where()->between('employees',50,1000);

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where object to add additional conditions. 

lt() | lte() | gt() | gte()

These methods are primarily for numeric fields, to check if a field's value is less than (<), less than or equal (<=), greater than (>), or greater than or equal (>=) to a specified value.

//Add Less Than Statement
$SugarQuery->where()->lt('gross_revenue',1000000);

//Add Less Than or Equal to Statement
$SugarQuery->where()->lte('net_revenue','500000');

//Add Greater Than Statement
$SugarQuery->where()->gt('gross_revenue',500000);

//Add Greater Than or Equal to Statement
$SugarQuery->where()->gte('net_revenue',100000);

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where object to add additional conditions. 

dateRange()

Used to check if a field's value is between a preset date range from the current time. See the TimeDate documentation on the available date range keys.

//add DateRange statement
$SugarQuery->where()->dateRange('date_modified','last_30_days');

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where object to add additional conditions. 

dateBetween()

To group the query on a field, you can use the corresponding groupBy() method. This method can be called multiple times, to add multiple fields to the grouping of the query.

//add group by
$SugarQuery->where()->dateBetween('date_created',array('2016-01-01','2016-03-01'));

Arguments

Returns

SugarQuery_Builder_Where Object

Allows for method chaining on the Where Object to add additional conditions. 

Combinations

Now that you have reviewed all of the available conditional statements for SugarQuery, you may want to combine them using AND and OR all within the same query. By default when the where() method is called, chained conditional methods will be added with AND to the where clause. You can specify an OR where clause on the main SugarQuery object by using the orWhere() method, which works the same as the where() method, just adds conditional statements with OR instead. The following methods allow for adding internal AND and OR logic to conditional statements on the Where object.

queryAnd()

To start a group of conditional statements that should all evaluate to True, use the queryAnd() method. For example, if you want to query for Accounts, where the name contains 'Test' AND description contains 'Test', you might use the following code:

$SugarQuery = new SugarQuery();
$SugarQuery->select(array('name'));
$SugarQuery->from(BeanFactory::newBean('Accounts'));

//Using queryAnd 
$SugarQuery->where()->queryAnd()->contains('name','Test')->contains('description','Test');

The above use of queryAnd() method isn't entirely needed, as the main Where object would be using AND for all conditions anyway, but it does group the two conditions inside of their own parenthesis in the compiled query, as shown below, to demonstrate how it can be used for altering query logic.

SELECT  accounts.name name FROM accounts WHERE accounts.deleted = 0 AND (accounts.name LIKE '%Test%' AND accounts.description LIKE '%Test%')

queryOr()

To start a group of conditional statements that should evaluate to true, if any condition is true, you can use the queryOr() method. For example, if you want to query for Accounts, where the name contains 'Test' or where the description contains 'Test', you might use the following code:

$SugarQuery = new SugarQuery(); 
$SugarQuery->select(array('name')); 
$SugarQuery->from(BeanFactory::newBean('Accounts')); 

//Using queryOr 
$SugarQuery->where()->queryOr()->contains('name','Test')->contains('description','Test');

This will group the two conditions inside of their own parenthesis in the compiled query. If either of the conditions is True, it will return a record. An example is shown below.

SELECT  accounts.name name FROM accounts WHERE accounts.deleted = 0 AND (accounts.name LIKE '%Test%' OR accounts.description LIKE '%Test%')