Tag Archives: SOQL

Introduction to Salesforce.com Object Query Language (SOQL)

Biswajeet   March 19, 2014   No Comments on Introduction to Salesforce.com Object Query Language (SOQL)

An introduction to Salesforce.com Object Query Language (SOQL):

As a developer looking to extend Salesforce.com, SOQL is very important and powerful aspect of coding. You can use SOQL to build your own custom query stings. These query strings can be used in the following places:

  • Apex statements
  • Visualforce getter methods and controllers
  • In the queryString param passed in the query() call
  • Finally, you can use the Schema Explorer in the Eclipse Toolkit

For those are familiar with SQL, will find some differences.

SOQL uses the “SELECT” statement combined with any filter statements to bring back sets of data. The data sets returned may be optionally ordered as well (just like in SQL).

Here is a basic SOQL Select example:

SELECT field1, field2, field3
FROM an object
WHERE filter statement(s) and (optionally) order the results

If you want to get all the names for opportunities created in the last 30 days.
you would use the following SOQL statement:

SELECT Name
FROM Opportunity
WHERE CreatedDate = Last_N_Days:30

If you want to get all the Leads from  your Salesforce.com account where the email address equals = “biswajeet@somecompany.com” you would use the following SOQL statement:

SELECT ID, Name from Lead WHERE email = 'biswajeet@somecompany.com'

SOQL – COUNT():

Getting the “Count” of results being returned in a SOQL data set is pretty simple as well.
For example, if I wanted to know how many Leads were going to be returned in my SELECT statement above, I can use the COUNT() function below:

SELECT COUNT() from Lead WHERE email = 'biswajeet@somecompany.com'

SOQL – Comparison Operators:

Operator Common name
= Equals
!= Not equals
< Less than
<= Less than or equal
> Greater than
>= Greater than or equal
IN In
NOT IN Not in (WHERE clause)
INCLUDES EXCLUDES Applies to multi-select picklists
LIKE Like (see section below)

SOQL – Like Operator:

The LIKE operator provides a way to match partial text strings and includes support for wildcards. Let’s say for a moment we want to find all the Leads where the email domain is the same. For this, we can use a “LIKE” operator.  He is an example of a LIKE statement with the % wildcard.

The placement of the percent sign ‘%’ is key here. I am basically saying, bring me back all the Leads where the email ends with “somecompany.com”. Therefore I place the ‘%’ at the beginning of whatever I am looking for.” Anything to the left of the % sign is ignored in the search. If I didn’t know the full domain I could use the following statement:

SELECT Id, Name from Lead WHERE email  LIKE '%somecompany.com'

This is going to return all the leads where the email contains “somecomp”.

SELECT Id, Name from Lead WHERE email  LIKE '%somecomp%'

Other wildcard is the underscore “_”. Thing is used to match exactly one character.
Note: Unlike with SQL, the LIKE operator in SOQL performs a case-insensitive match.

SOQL – WHERE/OR:

If you want to extend the WHERE clause to include multiple values, you can OR. See the example statement below:

SELECT ProductCode FROM PricebookEntry WHERE CurrencyIsoCode = 'USD' or CurrencyIsoCode = 'GBP'

Taking it a step further, you can evaludate multiple things in the WHERE clause:

SELECT ProductCode,UnitPrice FROM PricebookEntry
WHERE (UnitPrice >= 10 and CurrencyIsoCode='USD')
OR (UnitPrice >= 5.47 and CurrencyIsoCode='EUR')

Select All Fields with SOQL in Apex

Biswajeet   February 28, 2014   No Comments on Select All Fields with SOQL in Apex

Sample Code:

DescribeSObjectResult descResult = Account.getSObjectType().getDescribe();
List<String> fieldList = new List<String>(descResult.Fields.getMap().keySet());

String query = ' SELECT ' + String.join(fieldList, ',') + ' FROM ' + descResult.getName();
List<SObject> records = Database.query(query);

System.debug( records );

Select All Fields with SOQL in Apex by Using Record Id

Biswajeet   February 28, 2014   No Comments on Select All Fields with SOQL in Apex by Using Record Id

Sample Code:

Id recordId = '00128000002KuXO';

DescribeSObjectResult descResult = recordId.getSObjectType().getDescribe();
List<String> fieldList = new List<String>(descResult.Fields.getMap().keySet());

String query = ' SELECT ' + String.join(fieldList, ',') + ' FROM ' + descResult.getName() + ' WHERE ' + ' id = :recordId';
List<sObject> records = Database.query(query);

System.debug(records);

Using AggregateResult in Salesforce

Biswajeet   February 25, 2014   No Comments on Using AggregateResult in Salesforce

The aggregate functions COUNT(fieldname), COUNT_DISTINCT(), SUM(), AVG(), MIN() and MAX() in SOQL return an AggregateResult object or a List of AggregateResult objects. We can use aggregate functions result in apex by using AggregateResult object.

Here is an example to use AggregateResult in Salesforce. In below example I’m using COUNT(fieldname) aggregate function in SOQL to show Account record respective number of Contacts.

Visualforce Page:

<apex:page controller="SampleController" action="{!getData}">
    <apex:form >
        <apex:pageBlock >
            <apex:pageblockTable value="{!accWrapList}" var="acc">            
                <apex:column headerValue="Account Name" value="{!acc.AccountName}"/>
                <apex:column headerValue="Number of Contacts" value="{!acc.TotalContact}"/>
            </apex:pageblockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

Apex Class:

public with sharing class SampleController {
    public List<AggregateResult> result {get;set;}    
    public List<AccWrapper> accWrapList {get;set;}
    public List<Account> accList;
    public Map<Id, Account> accMap;
    List<Id> idList;
    
    public void getData() {
        accWrapList = new List<AccWrapper>();
        result = new List<AggregateResult>();
        idList = new List<Id>();
        accList = new List<Account>();
        accMap = new Map<Id, Account>();
        
        result = [SELECT COUNT(Id) Total, AccountId FROM Contact WHERE AccountId != null GROUP BY AccountId];               
        
        for(AggregateResult a : Result) {   
            idList.add((Id)a.get('AccountId'));
        }
        
        accList = [SELECT Id, Name FROM Account WHERE Id IN : idList];
        for(Account a : accList) {
            accMap.put(a.Id, a);            
        }
        
        for(AggregateResult aResult : result) {
            Account acc = accMap.get((Id)(aResult.get('AccountId')));
            accWrapList.add(new AccWrapper(aResult, acc.Name)); 
        }
    }
    
    public class AccWrapper {
        public Integer TotalContact {get;set;}
        public String AccountName {get;set;}
        
        public AccWrapper(AggregateResult a, String AccountName) {
            this.TotalContact =  (Integer)a.get('Total');
            this.AccountName = AccountName;
        }
    }
}

Output:

Aggregate Functions in SOQL

Biswajeet   February 25, 2014   No Comments on Aggregate Functions in SOQL

In database management an aggregate function is a function where the values of multiple rows are grouped together to form a single value. In Salesforce SOQL aggregate functions are same as SQL aggregate function.

The following aggregate functions are provided by SOQL:

  • AVG()
  • COUNT() and COUNT(fieldName)
  • COUNT_DISTINCT()
  • MIN()
  • MAX()
  • SUM()

AVG(): Returns the average value of a numeric field.

Example:

SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId

COUNT() and COUNT(fieldName): Returns the number of rows matching the query criteria. COUNT(Id) in SOQL is equivalent to COUNT(*) in SQL. COUNT(fieldName) available in API version 18.0 and later. If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT().

Example using COUNT():

SELECT COUNT()
FROM Account
WHERE Name LIKE 'a%'

Example using COUNT(fieldName):

SELECT COUNT(Id)
FROM Account
WHERE Name LIKE 'a%'

COUNT_DISTINCT(): Returns the number of distinct non-null field values matching the query criteria. COUNT_DISTINCT(fieldName) in SOQL is equivalent to COUNT(DISTINCT fieldName) in SQL. To query for all the distinct values, including null, for an object, see GROUP BY. Available in API version 18.0 and later.

Example:

SELECT COUNT_DISTINCT(Company)
FROM Lead

MIN(): Returns the minimum value of a field. If you use the MIN() or MAX() functions on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order. Available in API version 18.0 and later.

Example:

SELECT MIN(CreatedDate), FirstName, LastName
FROM Contact
GROUP BY FirstName, LastName

MAX(): Returns the maximum value of a field.
Example:

SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name

SUM(): Returns the total sum of a numeric field.
Example:

SELECT SUM(Amount)
FROM Opportunity
WHERE IsClosed = false AND Probability > 60

Note: You can’t use a LIMIT clause in a query that uses an aggregate function. The following query is invalid:

SELECT MAX(CreatedDate)
FROM Account LIMIT 1

The aggregate functions COUNT(fieldname), COUNT_DISTINCT(), SUM(), AVG(), MIN() and MAX() in SOQL return an AggregateResult object or a List of AggregateResult objects. You can use aggregate functions result in apex by using AggregateResult object.

Example:

List<AggregateResult> result = [SELECT COUNT(Id) Total, AccountId FROM Contact WHERE AccountId != null GROUP BY AccountId];