SOQL
This is Salesforce Object Query Language(SOQL) designed to query SFDC Database.
It is used to search records on a given criterion only in a particular SObject.
In soql query we can access 50000 records at the time.
SOQL queries limit:
Synchronous: 100
Asynchronous: 200
Syntax: SELECT one or more fields FROM an object WHERE filter statements and, optionally, results are ordered
Ex: [ SELECT ID, Name FROM Account WHERE Name=’acc1′];
You don't need to specify ID fields in the query as it is always returned in apex Queries. The only time to specify the ID fields in the query is if it is the only field you are receiving.
Instead of using equal(=) operator for comparison , you can perform fuzzy match by using “LIKE” : WHERE Name LIKE ‘Ashish’;
‘’ORDER BY’’ function is used to sort the return list by the SOQL Query.
You can limit the number of records returned arbitrary number by adding ‘’LIMIT n’’;
Use Case
Use SOQL when you know which objects the data resides in, and you want to:
Retrieve data from a single object or from multiple objects that are related to one another.
Count the number of records that meet specified criteria.
Sort results as part of the query.
Retrieve data from number, date, or checkbox fields.
Aggregate Functions In SOQL
Aggregate functions include AVG(), COUNT(), MIN(), MAX(), SUM(), and more.
You can also use aggregate functions without using a GROUP BY clause. For example, you could use the AVG() aggregate function to find the average Amount for all your opportunities.
AVG(): Returns the average value of a numeric field. For example: SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId
COUNT(): Returns the number of rows matching the query criteria.For example using COUNT(): SELECT COUNT() FROM Account WHERE Name LIKE 'a%'
MIN(): Returns the minimum value of a field. For example: SELECT MIN(CreatedDate), FirstName, LastName FROM Contact GROUP BY FirstName, LastName
MAX(): Returns the maximum value of a field. For example: SELECT Name, MAX(BudgetedCost) FROM Campaign GROUP BY Name
SUM(): Returns the total sum of a numeric field. For example: SELECT SUM(Amount) FROM Opportunity WHERE IsClosed = false AND Probability > 60
Any query that includes an aggregate function returns its result in an array of AggregateResult objects. Aggregateresult is readOnly sObject and is used for only query results.
EX : AggregateResult[] groupedResults = [SELECT AVG(Amount)aver FROM Opportunity ];
We can enforce security settings in SOQL -
WITH SECURITY_ENFORCED applies field- and object-level security checks only to fields and objects referenced.
Example : List<Account> act1 = [SELECT Id, Name FROM Account WITH SECURITY_ENFORCED]
Traversing in salesforce
Parent to Grand Child :
Parent(HouseOwner) -> Child(Broker) -> GrandChild(Tenant)
EX : SELECT Name, HouseOwner__r.Name, (SELECT Name FROM Tenants) FROM Broker__c ;
Grand Child To Parent :
GrandChild(Tenant) -> Child(Broker) -> Parent(HouseOwner)
Ex: SELECT Name, Broker__r.Name, Broker__r.HouseOwner__r.Name, From Tenant__c
What is Dynamic SOQL?
Dynamic SOQL refers to the creation of a SOQL string at run time with Apex code. For example, you can create a search based on input from an end user or update records with varying field names.
To create a dynamic SOQL query at run time, use the Database.query method. For Example :
String name = ’Avenoir’;
List<sObject> sobjList = Database.query('SELECT Id FROM CustomObject__c WHERE Name = : name’);
Dynamic SOQL can’t use bind variable fields in the query string. You can instead resolve the variable field into a string and use the string in your dynamic SOQL query:
String field = myVariable.field__c;
List<sObject> sobjList = Database.query('SELECT Id FROM CustomObject__c WHERE field__c = :field');
SOQL Limits on Big Object
When building an index query, do not leave gaps between the first and last fields in the query.
The !=, LIKE, NOT IN, EXCLUDES, and INCLUDES operators are not valid in any query.
Aggregate functions are not valid in any query.
To retrieve a list of results, do not use the Id field in a query. Including Id in a query returns only results that have an empty ID (000000000000000 or 000000000000000AAA).
SOSL
This is Salesforce Object Search Language. Searching the text string across the object and the field will be done using SOSL. It has the capability of searching a particular string across multiple objects.
Syntax :
FIND {SearchQuery} [ IN SearchGroup ] [ RETURNING FieldSpec] [ LIMIT n ]
Ex : Develop a query Account and Contact object for string ‘test’. SOLUTION: FIND {test} RETURNING Account(id LIMIT 20), Contact LIMIT 10
Performance Considerations
IN: Limits the types of fields to search, including email, name, or phone.
LIMIT: Specifies the maximum number of rows to return.
OFFSET: Displays the search results on multiple pages.
RETURNING: Limits the objects and fields to return.
WITH DATA CATEGORY: Specifies the data categories to return.
WITH DivisionFilter: Specifies the division field to return.
WITH NETWORK: Specifies the Experience Cloud site ID to return.
WITH PricebookId: Specifies the price book ID to return.
Key - Points
SOQL doesn’t support all advanced features of the SQL SELECT command. For example, you can’t use SOQL to perform arbitrary join operations, use wildcards in field lists, or use calculation expressions.
By using the SOQL for loops, You can avoid hitting the heap size limit.
for (variable : [soql_query]) { }
When you use Developer Console to generate a query from a resource, the Id field is included automatically. To query big objects in the Developer Console, remove Id from the generated query.
You can't sort on the fields like Rich Text and MultiSelect PIcklist.
Happy!! to help you add to your knowledge. You can leave a comment to help me understand how the blog helped you. If you need further assistance, please contact us. You can click "Reach Us" on the website and share the issue with me.
Reference :
Blog Credit:
A. Kumar
Salesforce Developer
Avenoir Technologies Pvt. Ltd.
Reach us: team@avenoir.ai