Salesforce Object Query Language SOQL
SOQL :
a. Salesforce Object Query language is termed as SOQL. The format looks similar to SQL of RDBMS systems.
b. SOQL is used to query the salesforce objects (custom or standard). Objects are nothing but tables/database in Salesforce Platform.
c. It returns maximum of 50,000 records in a transaction.
Syntax :
SELECT <fieldNames> from <OBJECTNAME> [ WHERE <Conditions> ] // Optional [ GROUP BY <Column Names> ] // Optional [ HAVING <Conditions> ] // Optional [ ORDER BY <Column Names> ] // Optional [ FOR UPDATE] // Optional [ LIMIT <NumberOfRecordsTo Return> ] // Optional [ OFFSET <NumberOf Records to Skip> ] // Optional [ ALL ROWS ] // Optional
Examples:
To retrieve name, industry from Account sObject
Select id,name,Industry from Account;
lastName, firstname from Contact
select id,lastname,firstname from Contact;
Stagename, amount from Opportuntiy
select stageName, Amount from Opportunity;
Different forms of SOQL in Apex.
1. List<Sobject> result=[query statement];
2. List<Sobject> result=Database.query(query statement);
3. Database.QueryLocator result=Databse.getQueryLocator(query statement);
4. Map<Id,Sobject> result=new Map<Id,Sobject>([query statement ]);
Examples:
In Apex:
1. SOQL to get all the Accounts for fields Name, Industry and Phone.
List<Account> result = [select Id, Name, Industry, Phone from Account];
2. To fetch all the Contact records for fields lastName, firstName
List<Contact> result=[select id, lastname, firstname from Contact];
3. Query all the Case records for Case Number, Origin
List<Case> cases = [select caseNumber, Origin from Case];
4. Query a custom object (say position__c) records for custom fields fields name, status, department
list<Position__c> posList = [Select name__c, status__c, department__c from position__c];
Different ways to execute the soql queries
1. Debug anonymous block from Developer Console
2. Query Editor in the Developer console
3. Workbench to test the query result (Recommended for the testing)
4. In Apex classes and Apex Triggers
5. Using sforce toolkit using Ajax in Visualforce pages
6. Debug Anonymous Window :
Workbench Tool :
Workbench is a powerful, web-based suite of tools designed for administrators and developers to interact with Salesforce.com organizations via the Force.com APIs.
1. Open the Url : https://workbench.developerforce.com/
2. Select the environment as Sandbox /production
3. Choose the api version
3. Login with salesforce account
4 Enter the salesforce account username/password
5. Select Soql from dropdown list
6. Select the object
7. Write the query
Ex : select name,industry from Account
8. Execute
SOQL Query statements can be written in two forms
1. Static query Statements
2. Dynamic query statements
When to go for static query.
If the query (the fields, conditions and Objects to be queried ) is not going to be changed during run time.
Example:
String query = 'select name,industry from Account'; Select name,industry from Account where Industry='Education';
String accIndustry = 'banking'; select name ,Industry from Account where Industry=:accIndustry
When to go for Dynamic Soql Query
When the query varies during run-time in terms of fields, conditions or Objects, the we need to build the query as string at run-time and execute the query using database methods.
String query = 'select name,Industry from Account';
if(accIndustry == 'Energy'){
query= query+'where Industry=\'Energy\' ';
} else {
query=query+' where phone=\'123\' ';
}
List<Account> result=Database.query(query);
Page to display Name, Industry, Phone, Id from Account.
public class SOQLDemo{
public List<Account> accList {set;get;}
public SOQLDemo(){
accList=[Select Id, Name, Industry, Phone From Account];
}
}
Page
<apex:page controller="SOQLDemo">
<apex:form>
<apex:pageBlock title="Soql Example">
<apex:pageBlockTable value="{!accList}" var="a">
<apex:column value="{!a.name}" />
<apex:column value="{!a.industry}" />
<apex:column value="{!a.phone}" />
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>
</apex:page>
Display Opporunity records using custom controller
public Class OppController {
public List<Opportunity> optyList {set;get;}
public OppController (){
optyList = [Select Name, StageName, Amount, CloseDate from Opportunity];
}
}
Page
<apex:page controller="OppController">
<apex:form >
<apex:pageBlock title="SoqlExample">
<apex:pageBlockTable value="{!optyList}" var="a">
<apex:column value="{!a.Name}" />
<apex:column value="{!a.StageName}" />
<apex:column value="{!a.Amount}" />
<apex:column value="{!a.CloseDate}" />
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>
</apex:page>
Example for LIMITS, ORDER BY, OFFEST
public with sharing class SOQLExample {
public list<account> result {get; set;}
public void setLimit(){
result = [select name, phone, industry from account LIMIT 10];
}
}
<apex:page controller="SOQLExample">
<apex:form >
<apex:pageBlock >
<apex:pageblockButtons >
<apex:commandButton value="Limit" action="{!setLimit}"/>
<apex:commandButton value="OffSet"/>
<apex:commandButton value="LimitOffset"/>
<apex:commandButton value="sortAscending"/>
<apex:commandButton value="ALLRows"/>
</apex:pageblockButtons>
<apex:pageblockTable value="{!result}" var="a">
<apex:column value="{!a.Id}"/>
<apex:column value="{!a.name}"/>
<apex:column value="{!a.phone}"/>
<apex:column value="{!a.industry}"/>
</apex:pageblockTable>
</apex:pageBlock>
</apex:form>
</apex:page>