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.
Now similar to SQL we can use LIMIT, ORDER BY, LIKE, WHERE
SELECT id, Name, createdDate FROM Account LIMIT 5
SELECT id, Name, createdDate FROM Account ORDER BY Name
SELECT id, Name, createdDate FROM Account ORDER BY Name DESC
SELECT id, Name, createdDate FROM Account WHERE NAME='coderinme'
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%'
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
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