Unlocking Advanced SOQL Features: Boosting Your SOQL Skills Tenfold

Hello Trailblazers, it's great to have you here again! In this post, I'll be sharing some advanced features and helpful tips for crafting SOQL queries. I'm confident that these pointers will enhance your proficiency when it comes to writing SOQL queries.

Date Functions

SOQL provides functions for Date manipulation, such as CALENDAR_MONTH(), CALENDAR_YEAR(), and DAY_ONLY(), allowing you to work with date and time fields effectively. You can pass date/date-time fields to these functions as parameters.

Example 1: Retrieve SObject records with specific date values in date-time fields while ignoring the time part using DAY_ONLY() function. 

This function transforms the date-time value into a date-only value which we can use to filter records based on date value only.

SELECT Id, FORMAT(CreatedDate) 
FROM Account 
WHERE DAY_ONLY(CreatedDate) = 2023-10-02

Example 2: Query SObject records with a specific month value in the date/date-time field with the help of the CALENDAR_MONTH() function. 

The CALENDAR_MONTH() function trims down date or date-time field value to month value only, so that you can filter the records based on month value.

SELECT Id, FORMAT(CreatedDate) 
FROM Account 
WHERE CALENDAR_MONTH(Createddate) = 10 

Example 3: Query SObject records where the date/date-time field value falls within the specific year with the help of CALENDAR_YEAR() function.

The CALENDAR_YEAR() function converts the date/date-time value to a year-only value so that we can filter the rows based on row values only.

SELECT Id, FORMAT(CreatedDate) 
FROM Account 
WHERE CALENDAR_YEAR(Createddate) = 2023

Example 4: Use CALENDAR_YEAR() and CALENDAR_MONTH() in the same query records that fall into a specific month and year.

By combining the above two functions we can filter records based on the year and date part of the date/date-time field and simply ignore the "day" part of it.

SELECT Id, FORMAT(CreatedDate) 
FROM Account 
WHERE CALENDAR_YEAR(CreatedDate) = 2023 AND CALENDAR_MONTH(CreatedDate) = 10

Date functions in Aggregate Queries

The functions CALENDAR_MONTH(), CALENDAR_YEAR(), and DAY_ONLY() can be also used in the SELECT clause if you are doing aggregate queries, these functions are very useful for analyzing the data based on the date, month, or year. Here are some examples of that.

Example 1: Group rows by date value. Let us say you want to find out the count of accounts created by each day.

SELECT DAY_ONLY(CreatedDate), Count(Id) FROM Opportunity GROUP BY DAY_ONLY(CreatedDate)

Group by DAY_ONLY(CreatedDate)

Example 2: Group rows by calendar month, the below query aggregates the number of opportunities created in each month of the year.

SELECT CALENDAR_MONTH(CreatedDate), Count(Id) 
FROM Opportunity 
WHERE CALENDAR_YEAR(CreatedDate) = 2023 
GROUP BY CALENDAR_MONTH(CreatedDate)

GROUP BY CALENDAR_MONTH(CreatedDate)

Example 3: Group rows by calendar year, the below query aggregates the number of opportunities created by each year.

SELECT CALENDAR_YEAR(CreatedDate), Count(Id) 
FROM Opportunity 
GROUP BY CALENDAR_YEAR(CreatedDate)

GROUP BY CALENDAR_YEAR(CreatedDate)

GROUP BY and HAVING

We all use the GROUP BY clause in SOQL, but how do we filter the rows based on aggregate values? Well, use the HAVING clause to filter the aggregate column values.

Example 1: For example see the below query which aggregates opportunity amounts.

SELECT Account.Name AccountName, SUM(AMOUNT) 
FROM Opportunity 
GROUP BY Account.Name

How can we filter these rows from the result where the amount is greater than ₹10,000? We can use the HAVING clause like this.

SELECT Account.Name AccountName, SUM(AMOUNT) 
FROM Opportunity 
GROUP BY Account.Name 
HAVING SUM(AMOUNT) > 10000

Basically, we filter the query results by using comparison operators on aggregated fields with the HAVING clause.

FORMAT() function

This function converts standard and custom numbers, date, time, and currency field values to localized format. For example, if you use this function on a date field it will convert that field value to your local time zone of the logged-in Salesforce user.

For number fields, it adds comma separators and for currency fields, it adds comma separators along with currency code.

SOQL result without format() function

SOQL result with format() function

Polymorphic Relationships

The standard Salesforce objects like Task and Event have some special lookup relationships called polymorphic, as the name suggests we can assign multiple different SObject type records to these fields. 

For example, the WhatId field on Task can take any record like Account, Opportunity, Custom objects, etc. So here are some tips to work with polymorphic relationship queries.

Example 1: Query records with specific SObject type. The below query will return only the event records that are associated with Account and Opportunity object records.

SELECT Id
FROM Event
WHERE What.Type IN ('Account', 'Opportunity')

 

Example 2: Query specific fields from each different Sobject type, for example, the below query selects Phone and NumberOfEmployees fields from WhatId when it is an Account record and Amount and CloseDate when it is an Opportunity record.

SELECT 
  TYPEOF What
    WHEN Account THEN Phone, NumberOfEmployees
    WHEN Opportunity THEN Amount, CloseDate
    ELSE Name, Email
  END
FROM Event

 References

FOR UPDATE

When you query records from Apex in order to update them, you can use the FOR UPDATE clause to make sure that no other thread is accessing those records while your Apex code is updating them. No other user or process is allowed to update the record until your transaction is completed.

Even users can't edit the records from the browser via the Salesforce user interface.

See the code example below: Where the two account records are queried using the FOR UPDATE clause.

Account [] accts = [SELECT Id FROM Account LIMIT 2 FOR UPDATE];

Important Notes: 

• You can't use the ORDER BY clause with FOR UPDATE.
• FOR UPDATE can cause record-locking issues if not used correctly.

 References

FOR VIEW

The FOR VIEW clause is used to update the record's LastViewedDate so that the record is visible in the RecentlyViewed list view. I know this is not very frequently used but good to know. I have used this feature along with a custom lookup component in LWC, to show last accessed records.

Check out the reference and the FOR REFERENCE clause as well.

Query Optimization

Have your SOQL queries ever timed out or slowed down? If yes then this tip is very important for you. Whenever the query times out, there are two reasons behind that. One is a large data set and the second is a non-selective query condition.

Whenever you run a query with the non-selective condition, it slows down the process or eventually times out. To avoid that I have some tips.

1. Always put the conditions with indexed fields at the beginning of the WHERE clause when you have multiple conditions, it helps queries to run faster as the indexed fields help to accelerate searching. 
   
   Putting these types of fields at the beginning will result in faster query results.
   
1. Id fields
2. Standard Name fields.
3. Standard Date/DateTime fields like CreatedDate, LastModifiedDate, etc.
4. Indexed fields
5. External ID fields
6. relationship fields
2. Check if you can convert any field/s to indexed fields, and convert.
3. Give priority to fields with more distinctive values in the WHERE clause, meaning fields in which you are sure that the particular condition will result in fewer records.

Let us understand this with a simplified example, see the below query, can you identify what is wrong with it?

SELECT Id, Name 
FROM Account 
WHERE Name LIKE '%Fin%' and CreatedDate = THIS_YEAR

Well nothing, but by changing the condition order we can make it perform faster because the CreatedDate = THIS_YEAR condition is selective and improves the record searching. So here is the better version of the above query.

SELECT Id, Name 
FROM Account 
WHERE CreatedDate = THIS_YEAR AND Name LIKE '%Fin%' 

This example is oversimplified but it gives an idea to optimize the SOQL queries.

Thanks for reading! Let me know in the comments if you know such tips on SOQL.