SOQL (Salesforce Object Query Language)

SOQL is the Salesforce Object query language, it is similar to SQL in syntax. SOQL is a tool which helps us to retrieve a record from database in Salesforce. A sample SOQL is written below:

Select Id, Name from Account;

The result of the above statement is a list of accounts, i.e. a SOQL returns a list of sObject. We can rewrite the above statement as below.

List<account> examplelist = [Select Id, Name from Account];

Individual records can be accessed from this list using a loop. We can use SOQL to create a new sObject as well.

Contact newContactObject = new contact(Account = [select name from account where billingCity = ‘new delhi’ limit1 ];)

newContactObject.firstname = ‘test’;

newContactObject.lastname = ‘contact’;

Working with the result of SOQL Query

The result of the query only include the fields which are mentioned in the query, if we try to access a field which was not fetched in the query then we end up having a runtime error.

Account a = [Select id from account limit 1].name    —- this will throw runtime error

Account a = [Select id,name from account limit 1].name    —- this will run uninterrupted

Accessing fields of sObject through relationship in SOQL

There are two types of relationship when it comes to fetching record from SOQL

1. Fetching values of parent record from a child record
2. Fetching all the child records of a parent.

The first scenario is achieved using a dot notation in SOQL query as written below:

Select id, name, parentobj.name, parentobj.custfield__c from myObject__c limit 1;

The second scenario is achieved by using a nested query as mentioned below:

Select id, name, (select firstname, lastname from contacts) from account;

When we write a nest query we make use of the child relationship name as ‘contacts’ is the relationship name between account and contact, we have used the plural of contacts. It is a user defined text for a custom relationship.

In the custom relation we make use of ‘__r’ instead of the regular ‘__c’ keyword, as written below.

Select Id, name, custObject__r.name  from contact limit 1;

*Note, it is advised that we should avoid searching for records which contains a null value, this helps in improving the performance of the query.

How to use Apex variable in SOQL Queries

SOQL can reference apex variable in the statement, variable has to be prefixed with a colon(:) as written below:

Contact con = new contact(lastname = “test”, firstname =”contact”);

Insert con

Select name from contact where Id =:con.Id;

We can use apex variable in the where clause, In or Not In clause, numeric values of limit or offset.

SOQL can also fetch the records which have been soft deleted from the org i.e. they are still present in recycle bin. We just have to add literal ALL ROWS at the end of the query to fetch all the records in an org, as mentioned below.

Select name from contact ALL ROWS;

How to write SOQL for a for Loop

Records returned by the SOQL can be used as an input for for loop the syntax is written below.

For( myVar : [Query]){

}

Variable should be of same sObject type as of the SOQL query.

Regular SOQL and SOQL in the for loop differ from each other on the basis of the method they use to fetch record from the database, SOQL for loop use efficient chuning of data and make calls to query and querymore method where as standard SOQL can only return number of records or number of sObjects.

We should make sure that we are not making any DML call inside for loop as it results in breach off governor limit. Therefore we should use a list of sObject and add the individual objects to this list and then after the execution of the loop we should perform a DML Operation.

Next: Playing with sObject Collections