SOQL And SOSL in Apex in Salesforce

In Computer Science, Data is everything, to store data we cerate Database, then we use multiple techniques to fetch and save/delete the data. DML(data manipulation Language) is used to insert, update or delete the record in the database. SQL(structured Query Language) to fetch records/data from the database.

Similarly, Salesforce use SOQL (Salesforce Object Query Language) to fetch the record. SOQL is very powerful as it has almost all features of MySQL, MsSQL etc and also it has rich keyword collection that can be used to fetch data according to our need, without writing extra code.

Let’s take one example. Did you use MySQL?

SELECT * FROM Account

In SOQL, SELECT * is not allowed, we have to choose all fields one by one If want to query like

SELECT id, Name, createdDate FROM Account

you can try and learn SOQL using Query editor of Developer Console.

SOQL

Now similar to SQL we can use LIMIT, ORDER BY, LIKE, WHERE

LIMIT

SELECT id, Name, createdDate FROM Account LIMIT 5

SOQL

ORDER BY

SELECT id, Name, createdDate FROM Account ORDER BY Name

SOQL

SELECT id, Name, createdDate FROM Account ORDER BY Name DESC

WHERE

SELECT id, Name, createdDate FROM Account WHERE NAME='coderinme'

SOQL

LIKE According to SFDC LIKE operator in SOQL and SOSL is similar to the LIKE operator in SQL; it provides a mechanism for matching partial text strings and includes support for wildcards.

  • LIKE operator is supported for string fields only, the string used in LIKE will be case insensitive.
  • % and _ will be used as wildcard in LIKE, The % wildcard matches zero or more characters. The _ wildcard matches exactly one character.

SELECT id FROM Account WHERE Name LIKE '%Coder%'

GROUP BY

SELECT COUNT(id),Industry FROM Account GROUP BY Industry

SUM , AVG are also a method that can be used with Group By

Inner Query (FROM Parent to child)

Suppose we want to find All the contacts related to an Account, with Account details also. there are 2 ways

Account acc = [SELECT Name, id FROM Account WHERE id='0010S0S0S0S0A'];
List<Contact> contList= [SELECT lastName FROM Contact WHERE AccountId='0010S0S0S0S0A'];

We can merge it into 1 SOQL and make it more optimized.

Account acc = [SELECT Name, id,(SELECT lastName FROM Contacts) FROM Account WHERE id='0010S0S0S0S0A'];

This means We can query child record into a parent Query as inner query . We know we must follow some rules as we are part of salesforce a cloud based system , we can write only 100 queries in one transaction. or we can say in a single transacation, our queries can be executed only 100 times.

So we must optimized the code to work within limits.

Now When we write  a query on parent, so we can write inner query upto 1 level , it means we can access contact from Account but not the child of contact. Now We will access the child from Query.

Account acc = [SELECT Name, id,(SELECT lastName FROM Contacts) FROM Account WHERE id='0010S0S0S0S0A'];
List<Contact> contList = acc.Contacts;

How to write inner query

When we will use the inner query we must find the child relationship name on the parent field of child object. You can see below on Child object Contact we opened the Account filed and checked the Child relationship name.

Now if it is custom object then it will be like Child Relationship Name and __r

Like Account is the parent of Salary.So SOQL will be like

[SELECT id,(SELECT id FROM Salaries__r) FROM Account]

Points to Remember :  Please check Child Relationship Name before using it into inner query.

Child to Parent

We can query the parent fields in child object Query.

[SELECT id,Account.Name FROM Cntact]

We can go upto 5 level of parents from Child. For Example

Account is child of Project , Contact is child of Account, Salary is child of Contact. We can use project fields in Contact Query.

[SELECT id,Contact__r.lastName FROM Salary__c]

[SELECT id,Contact__r.Account.Name FROM Salary__c]
[SELECT id,Contact__r.Account.Property__r.Name FROM Salary__c]

Date / Datetime in SOQL

SOQL has very rich date literals that can be directly use in query. TODAY, NEXT_WEEK , LAST_WEEK etc.If we want to specify a date We can use

SELECT Id FROM Account WHERE CreatedDate > 2016-10-08T01:02:03Z
SELECT id from Conatct where Salary_date__c= 2019-09-22

We want to check all opportunity that is created yesterday

SELECT Id FROM Opportunity WHERE CreatedDate = YESTERDAY

List of Literal that we can use

YESTERDAY, TODAY, TOMORROW, LAST_WEEK, NEXT_WEEK, THIS_WEEK, THIS_MONTH, LAST_MONTH, LAST_n_DAYS:n

SELECT Id FROM Opportunity WHERE CreatedDate < THIS_MONTH


SELECT Id FROM Opportunity WHERE CreatedDate = LAST_YEAR

LAST 3 days 
SELECT Id FROM Opportunity WHERE CreatedDate = LAST_N_DAYS:3

THIS_QUARTER

LAST_N_MONTHS:n.

Please check salesforce for all literals

Please use only those fields in query which we are going to use in code, Don’t use SOQL inside for loop, Please Use filters logic so that data will be actual correct and processing time will be fast. Please use LIMITS as we can only take 50,000 records in a soql query , 200 no of soql is allowed.Don’t use NOT IN keyword.

For large volume of that , please look into this consideration

We will discuss SOSL in next part.

All rights reserved. No part of this Post may be copied, distributed, or transmitted in any form or by any means, without the prior written permission of the website admin, except in the case of brief quotations embodied in critical reviews and certain other noncommercial uses permitted by copyright law. For permission requests, write to the owner, addressed “Attention: Permissions Coordinator,” to the admin @coderinme

 

A web developer(Front end and Back end), and DBA at csdamu.com. Currently working as Salesforce Developer @ Tech Matrix IT Consulting Private Limited. Check me @about.me/s.saifi

Leave a reply:

Your email address will not be published.