» » Filtering records :- Where, LIKE, IN, NOT IN, INCLUDES, EXCLUDES, Semi-Joins with IN and Anti-Joins with NOT IN, ORDER BY, GROUP BY, OFFSET in Salesforce SOQL - SOQL Basic | Salesforce Funda

Filtering records :- Where, LIKE, IN, NOT IN, INCLUDES, EXCLUDES, Semi-Joins with IN and Anti-Joins with NOT IN, ORDER BY, GROUP BY, OFFSET in Salesforce SOQL - SOQL Basic | Salesforce Funda

 



Filtering records with various operators let's check all one by one Where, LIKE, IN, NOT IN, INCLUDES, EXCLUDES, Semi-Joins with IN and Anti-Joins with NOT IN, ORDER BY, GROUP BY, OFFSET


Where :- 

for filtering record we used "Where" keyword with various condition's

 SOQL syntax example:-
  
//query with where filter condition Name equal to 'Sovil Singh' 
Select Id, Name From Account Where Name = 'Sovil Singh'
//query with where filter condition Id equal to '0015g00000sqOBDAA2' 
Select Id, Name From Account Where Id = '0015g00000sqOBDAA2'

Like :-

LIKE operator performs a case-insensitive match. It supported only string fields. 
Some features we need to understand while using like Operator :
  • The % and _ wildcards are supported for the LIKE operator.
  • The % wildcard matches zero or more characters.
  • The _ wildcard matches exactly one character.
  • The text string in the specified value must be enclosed in single quotes.
  • The LIKE operator is supported for string fields only.
  • The LIKE operator performs a case-insensitive match, unlike the case-sensitive matching in SQL.
  • The LIKE operator in SOQL and SOSL supports escaping of special characters % or _.
  • Don’t use the backslash character in a search except to escape a special character.

  
//contains Sovil
Select Id, Name From Account Where Name Like '%Sovil%'
//Start with Sovil
Select Id, Name From Account Where Name Like 'Sovil%'
//end with Sovil
Select Id, Name From Account Where Name Like '%Sovil'
//Start with sovil after this contain any characters
select Id, Name From Account Where Name Like 'Sovil_%'
//Start with S any characters after S V L
select Id, Name From Account Where Name Like 'S_v_l_%'


IN :-

 IN Operator is used to specify multiple values in the WHERE clause for matching and filtering records, the values for IN must be in parentheses. String values must be surrounded by single quotes

We use IN operator in SOQL the data is fetched from the matched values specified in the SOQL statement

SOQL Syntax :-



Select Id, Name From Account Where Name IN('Sovil', 'Sagar', 'Sahil')


NOT IN :-

 NOT IN Operator is used to specify multiple values in the WHERE clause for matching and filtering records which is not present in values which we provided, the values for NOT IN must be in parentheses. String values must be surrounded by single quotes

We use NOT IN operator in SOQL the data is fetched from the NON matched values specified in the SOQL statement

SOQL Syntax :-



Select Id, Name From Account Where Name NOT IN('Sovil', 'Sagar', 'Sahil')

Includes, Excludes :-

SOQL Includes and excludes operators  are mainly used to filter multipicklist field values in salesforce. These operators are used for only multipicklist values. Multipicklist values are those whose have more than one picklist values 

Includes operator is used to filter and retrieve the data that contains any of the specified values in SOQL statement.

Excludes operator is used to filter and retrieve the data that not contains any of the specified values in SOQL statement.

SOQL Syntax :-



//Includes
Select Id, Name From Application Where Skill INCLUDES('Salesforce', 'SOQL', 'Apex')
//Excludes
Select Id, Name From Application Where Skill Excludes('JAVA', 'SQL', 'C#')

Semi-Joines and ANTI Joins :-

Semi-Joins :- we can use semi join using IN with Where condition with related object 

SOQL Syntax


Select Id, Name from Account Where ID IN (Select AccountId From Contact Where AccountID != null

Anti-Joins :- we can use anti join using NOT IN with Where condition with related object 

SOQL Syntax


Select Id, Name from Account Where ID NOT IN (Select AccountId From Contact Where AccountID != null

Note :-  

You can’t query on the same object in a subquery as in the main query.
You can’t use subqueries with OR.
COUNT, FOR UPDATE, ORDER BY, and LIMIT aren’t supported in subqueries.

ORDER By :-

ORDER BY can be used in a SOQL Select statement to control the order of the query results

When using ORDER BY we can specify whether the data is sorted in ascending or descending can be used. the default order is ascending.


//order by Name 
select Id, Name, Industry FROM Account ORDER BY NAME
//if with the same we have same record we can use unique fields
select Id, Name, Industry FROM Account ORDER BY NAME, ID
//Ascending (A-Z)
select Id, Name, Industry FROM Account ORDER BY NAME ASC
//Descending(Z-A)
select Id, Name, Industry FROM Account ORDER BY NAME DESC
//Ascending (Date)
select Id, Name, Industry, CreatedDate FROM Account ORDER BY CreatedDate ASC
//Descending(Date)
select Id, Name, Industry, CreatedDate FROM Account ORDER BY CreatedDate DESC
//Ascending (BY Default Take null first)
SELECT Name, Industry  FROM Account ORDER BY Industry ASC NULLS FIRST
//Descending(Date)
SELECT Name, Industry  FROM Account ORDER BY Industry DESC NULLS LAST

LIMIT :-

LIMIT is an optional clause that can be added to a SELECT statement of a SOQL query to specify the maximum number of rows to return.

SOQL syntax :- 

SELECT Id, Name FROM Account WHERE Industry = 'Technology' LIMIT 50

This query returns the first 50 Account records whose Industry is 'Technology'.

You can't use a LIMIT clause in a query that uses an aggregate function, 
but does not use a GROUP BY clause. For example, the following query is invalid:
You can use LIMIT with count() as the fieldList to count up to the maximum specified.

SOQL syntax :- 

//throw error ==> Non-grouped query that uses overall aggregate functions cannot also use LIMIT
SELECT MAX(CreatedDate) FROM Account LIMIT 1
//Correct Query look like 
Select MAX(CreatedDate), ID, Name from Account Group By Id, Name Order By MAX(CreatedDate) DESC LIMIT 1

  


OFFSET :-

When expecting many records in a query’s results, you can display the results in multiple pages by using the OFFSET clause on a SOQL query.
for Example we have :- 5o account records and we want to see records from 21 to 50 then we can offset first 20 records Example

SOQL syntax :- 




Select Id, Name From Account Limit 50 OFFSET 20
//More accurate way
SELECT Id, Name From Account ORDER BY NAME LIMIT 25 OFFSET 10

  


Considerations When Using OFFSET 
Here are a few points to consider when using OFFSET in your queries: 

  • The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 results in a NUMBER_OUTSIDE_VALID_RANGE error. 
  • The OFFSET clause is allowed in SOQL used in SOAP API, REST API, and Apex. It’s not allowed in SOQL used within Bulk APIs or Streaming API. 
  • OFFSET is intended to be used in a top-level query, and is not allowed in most subqueries, so the following query is invalid and returns a MALFORMED_QUERY error:
SOQL Syntax :-


//Give error ==> MALFORMED_QUERY
select Id, Name  FROM Account WHERE Id IN ( SELECT AccountId FROM Contact LIMIT 100 OFFSET 20 ) ORDER BY Name


A subquery can use OFFSET only if the parent query has a LIMIT 1 clause. The following query is a valid use of OFFSET in a subquery

SOQL Syntax :-




SELECT Name, Id,( SELECT Name FROM Opportunities LIMIT 5 OFFSET 2 ) FROM Account ORDER BY Name LIMIT 1




Feature :-

 


























Labels:
,