Before reading this, you must read the article https://infotechabout.com/understand-soql/ for understanding salesforce objects or the Use of SELECT statements in the salesforce developer console.
SELECT Name, Status, Company, Email FROM Lead
SOQL SELECT WHERE CLAUSE
In this article, we are going to learn how we can apply query or filter records from salesforce objects. For example, we only want to query those lead records which have a status as closed or converted. You can define your conditions or your filters in a SOQL statement in a Where clause and a Where clause starts with a special keyword called WHERE. So, here we have our SOQL query, which we have used to retrieve the records from the lead object. Now we want to filter out the records based on the values in the status field.
So, after this query or after the lead word, you can also write a special keyword called WHERE, and you can start defining your condition there.
For example, this is how you can define a condition on the status field.
You will write “WHERE status = ‘closed – converted'” and similar to the select and from keyword, WHERE is also a keyword in a SOQL statement and after the WHERE keyword, you define the condition, like the way we are doing it here. We are putting a condition on the status field and we are saying the status field’s value should be closed converted. So, this query is only going to retrieve those records where the status is equal to close converted. Let’s go to the Developer console and try out the following query.
// Select Query with WHERE Clause
SELECT Name, Status, Company, Email FROM Lead WHERE Status=’Closed – Converted’
Retrieve all the records from the lead object where the status is equal to Closed – Converted.
SOQL Select Multiple Conditions with OR and AND operator.
Where Clause can accept multiple conditions.
// Select Query with WHERE Clause with 02 conditions with OR (Logical Operator) means at least one condition must be True.
SELECT Name, Status, Company, Email FROM Lead WHERE Status=’Closed – Converted’ OR Status=’Closed – Not Converted’.
Retrieve all the records from the lead object where the status is either equal to Closed – Converted or it is equal to Closed – Not Converted.
// Select Query with WHERE Clause with 02conditions with AND (Logical Operator) mean at both conditions must be True.
SELECT Name, Status, LeadSource, Company, Email FROM Lead WHERE Status=’Closed – Converted’ AND LeadSource=’Web’
Retrieve all the records from the lead object where the status is Closed – Converted and the lead source is the web.
SOQL Select IN Keyword
Add multiple possible values for a field. It can give better performance in some circumstances but it’s not guaranteed and can only be confirmed by the query execution plan.
// Select Query with WHERE Clause with IN Operator for Multiple Possible values for a single field.
SELECT Name, Status, LeadSource, Company, Email FROM Lead WHERE Status IN (‘Closed – Converted’, ‘Closed – Not Converted’) AND LeadSource=’Web’
Retrieve all the records from the lead object where the status is either equal to Closed – Converted or it is equal to Closed – Not Converted and Lead Source is the web.
SOQL Select Like Keyword
Match partial text string with support of wildcards. % wild matches zero or more characters. Like ‘Closed%’ & ‘%Closed%,’ All possible matches start or in between.
The _ wildcard matches exactly one character Like ‘_lose’ means start with any character but after the first character, there should be lose.
// Select Query with WHERE Clause with Like Operator (Wildcard support & Partially Match Text String).
SELECT Name, Status, LeadSource, Company, Email FROM Lead WHERE Status LIKE ‘Closed%’ AND LeadSource=’Web’
Retrieve all the records from the lead object where the status which has closed word in between somewhere and Lead Source is the web.
SOQL Select Order by Clause (Sorting of Records)
So far, we have only been discussing how to retrieve results or how to filter out your records, but how about sorting your records?
For example, I want to sort my results by name.
How can I do that? For this, we have a very simple keyword in SOQL to sort your records either ascending or descending by any of the field values, except the large fields like text area or some encrypted fields. But generally, you are only sorting on the normal text fields or number fields or maybe on the date fields, which are all supported in the Order By clause. So this Order By clause, you can write after your Where condition or at end of the Select statement. If there is no condition then it will be used after FROM <objectname>.
// Select Query with ORDER BY Clause
SELECT Name, Status, LeadSource, Company, Email FROM Lead ORDER BY Name
Retrieve all the records from the lead object sorting by Name in Ascending order (default)
// Select Query with ORDER BY Desc Clause
SELECT Name, Status, LeadSource, Company, Email FROM Lead ORDER BY Name DESC
Retrieve all the records from the lead object sorting by Name in Descending order.
// Select Query with Where and ORDER BY Clause
SELECT Name, Status, Company, Email FROM Lead WHERE Status=’Closed – Converted’ ORDER BY Name
Retrieve all the records from the lead object where the status is Closed – Converted and
sorted by Name
SOQL Select Limit Clause
let’s now talk about limiting your search results using the SOQL statement and adding an
offset value. Here I have a simple query, which retrieves all the records from the lead object, and as you can see, I have 22 records in my lead object.
// Query to Retrieve all records from Lead object
SELECT Name, Status, LeadSource, Company, Email FROM Lead
However, If you only want to retrieve the first 10 records then you can limit your search result by using the keyword LIMIT and then specifying the limit value.
So, you can simply do something like this, LIMIT 10 and this time we are only going to get the top ten records.
// Select Query with Limit Clause.
SELECT Name, Status, LeadSource, Company, Email, CreatedDate FROM Lead LIMIT 10
Retrieve the Top 10 records from the lead.
Now, in most of the use cases, what you want is, you want to retrieve the latest record that got created. So in those situations, you can use your Order By clause and LIMIT keyword together. So, you can use
// Select Query with Limit with Order By clause
SELECT Name, Status, LeadSource, Company, Email, CreatedDate FROM Lead Order By Name LIMIT 10
Retrieve First Top 10 records from the lead.
However, in most of your case, what you want is, you want to retrieve the latest record that got created so the above query only retrieves the first Top 10 records from the lead mean its Ascending order only oldest created record will come first
So in this situation, you can use your Order By clause and LIMIT keyword together you sort them in descending order so that your newly created records come on top and then you can limit the result size using the LIMIT keyword. So, this is how we will do it, Order By Created Date.
// Select Query with Limit with Order By clause with Desc
SELECT Name, Status, LeadSource, Company, Email, CreatedDate FROM Lead ORDER BY CreatedDate DESC LIMIT 1
I am Trying Retrieve Latest Top 10 records from the lead.
SOQL Select OFFSET Clause
With the above query, you won’t retrieve the latest Top 10 records. So, you will do Order By Created Date and then we will sort it in descending order, and then we will limit the output to only one. And this way, we will only get our latest record. The interviewer can make some changes in this question, maybe like, he will say, give me like top ten records instead of only one or give me top 20 records, or he can make it more challenging for you by asking that I don’t want to retrieve the first five records, but give me the top five records after that. For example, if we limit this value by 10, and we get 10 records here, then the interviewer may ask you that he’s not interested in these top 5 records, but give him the remaining five. So, how can you do that? So, in SOQL we have got one more keyword, which is called Offset, and this Offset keyword is used to point to a particular row.
For example, if I want to retrieve all the records from the lead, but I want to ignore the first five records. So what I can do, I can simply use an Offset keyword here and the value of offset and this offset value can be from one to two thousand. So, the maximum offset that you can set is two thousand. And if you run this query,
// Select Query with OFFSET clause
SELECT Name, Status, LeadSource, Company, Email, CreatedDate FROM Lead OFFSET 5
Retrieve all records from the lead object after 5.
You will notice that you have got 17 rows instead of the 22 that you were getting before because you have set our pointer to the sixth record first and then we started retrieving the result. So, it has skipped the first five records and we can confirm that from our result. Here our result starts from “Brenda”, which is the sixth record in our previous result, and we can use this Offset along with LIMIT to answer your interview question, where the interviewer is asking you to skip the first five rows and then get the remaining five, so we can use Order By first, and we will order by Created Date and then we will limit our search result to only five because the interviewer is only interested in top five records, but he wants you to skip the first top-five and get the remaining five after that. So this way, let’s remove the Offset first and we will see, what is the result that we get. So this way, let’s remove the Offset first and we will see, what is the result that we get. So, we get from Bertha to Patricia, and let’s also sort it in descending order. So, here we get top-five lead records and our last record is Patricia. But as soon as you add your Offset keyword and skip the five rows, then it’s going to skip all these five and it’s going to pick the next five, which are satisfying this order by clause and this limit keyword, and let’s execute it. And now we are getting a different result.
// Select Query with Order by Desc clause, Limit, and Skip Clause
SELECT Name, Status, LeadSource, Company, Email, CreatedDate FROM Lead ORDER BY CreatedDate DESC LIMIT 5 OFFSET 5
Retrieve the latest five records after 5 means you have skipped the first 5 records through the offset clause and Limit 5 only shows 5 records after 5 and they are in descending order.
SOQL Operators to be used in Where clause conditions.
Now Let’s Discuss the SOQL operators, and these are very similar to the operators that we have seen in Apex as well, like the Equals To the operator, the Less Than operator, and the Greater Than operator. So, we have similar operators in SOQL as well.
Working Date and DateTime Type Fields
When you’ll be writing your SOQL quires, you will realize that most of the time you’ll be working with Date or DateTime fields, and working with Date or DateTime fields can be a bit challenging sometimes, as you need to format your field value in a specific format before you supply these date or DateTime values to your SOQL query.
SOQL supports multiple DateTime formats.
If your field is a Date field, then you can supply your value in this format.
First, you need to pass your year value, then there should be a dash, then the month value, then a dash again, and then the date value.
So, this is the format that SOQL supports for the date fields.
But if your field is a DateTime field, then SOQL supports 3 types of formats and these formats are designed to support the time zone as well.
And as you can see from these three formats, it can be a bit challenging to format your values in these formats before you supply those to your SOQL query.
How can you make this task easier? If you’re comparing something very simple then SOQL supports Date literals as well, and these date literals are here to compare a range of values and obviously to make our task easier.
For example, if you only want to compare the records that are created today, or maybe if you want to compare any other date field with today’s value, you can simply write today in your SOQL query and your SOQL will understand that you are comparing with today’s value.
And this Today literal means the entire 24-hour duration of today, which starts from 00 AM, and very similar to this today literal, we have yesterday and tomorrow as well, where yesterday compares with yesterday’s date and tomorrow compares with tomorrow’s date.
And you can use these date literals with both date fields and DateTime fields.
And these are not the only data literals that SOQL supports.
We have a lot of date literals available for us.
For example, if you want to compare the last week’s date, then you can use Last_week,
for this week, you can use This_week and for the next week, you can use Next_week.
And the same literals are supported for the month, year and quarter as well. And along with this quarter and year, we have got a bunch of literals for the fiscal quarter and fiscal year as well, which are different for each and every Salesforce Org.
So, let’s say if your company’s fiscal year starts from February, then these fiscal year values will be different for you than some other company whose fiscal year starts from maybe September. And not only this, we have got some advanced date literals as well, which are really helpful when you are dealing with a date or DateTime fields like Last_N_Days, so let’s say if you want to compare for last 50 days, then you can supply a value of N as 50.
Very similar to the last N days, we have got the next N days as well, where you can supply any value of N, where N’s value should be in a number of days.
But if you want to compare in weeks, then we have got the last N weeks and next N weeks as well. We have also got the last N months, next N months, last N year, and next N year as well. And as you have guessed, we have also got last N quarter, next N quarter as well.
// Get all leads created on 10th June 2022
SELECT Name, Status, LeadSource, Company, Email, CreatedDate FROM Lead WHERE CreatedDate>2022-06-10T00:00:00.000+0000
// Select all leads created today
SELECT Name, Status, LeadSource, Company, Email, CreatedDate FROM Lead WHERE CreatedDate=TODAY
// Select all leads created after yesterday
SELECT Name, Status, LeadSource, Company, Email, CreatedDate FROM Lead WHERE CreatedDate>YESTERDAY
// Select all leads created in the last 10 days
SELECT Name, Status, LeadSource, Company, Email, CreatedDate FROM Lead WHERE CreatedDate=LAST_N_DAYS:10
I hope you are more comfortable working with SOQL date and datetime fields now. In the next Article, we will be discussing Relationships Queries / Join Queries.
If you have any queries feel free to comment or contact us through email yousuf@infotechabout.com
