What is the basic SOQL statement?
[SELECT fieldNames FROM objectName]
- fieldNames is the API name of the field we want to retrieve or comma-separated list of API names of the fields we want to retrieve
- objectName is the API name of the object that we want to execute our query against
- we must include at least one field in the SELECT clause of our query and exactly one object in the FROM clause of our query
What are the optional SOQL clauses we can use?
- WHERE
- WITH
- GROUP BY/HAVING
- ORDER BY
- LIMIT
- OFFSET
- FOR REFERENCE
- FOR UPDATE
What does the WHERE clause do?
it’s a filter for which records we want to return
List edgeAccs = [
SELECT Name
FROM Account
WHERE Name = ‘Edge Communications’];
What does the WITH SECURITY_ENFORCED clause do?
- a query using this clause will take the object and field level permissions of the user executing the query into account
- if the query is operating on a field or object that the running user doesn’t have read access to, an exception will be thrown and nothing will be returned
- this is one of three ways we can enforce object and field level security in our Apex code
What are the two main SOQL return types?
- a single sObject (a single record, e.g. we can write queries with a return type of Account)
- a List of sObjects (e.g. List)
What is the purpose of Variable Binding in SOQL?
- Variable Binding allow us to make use of variables from Apex as filters
- to perform variable binding, precede name of variable with “:”
String accountName = ‘Edge Communications’;
List edgeCommunications = [
SELECT Id
FROM Account
WHERE Name=:accountName]
What are the six aggregate functions that we can use in SOQL queries?
- COUNT,
- COUNT_DISTINCT,
- MIN,
- MAX,
- SUM,
- AVG
NOTE: when we write a SOQL query that only contains the COUNT function in its SELECT clause (without an argument) and no grouping, the return type of the query will be Integer
Integer numOfContacts = [SELECT COUNT() FROM Contact];
What is the purpose of the get() method in AggregateResult?
the AggregateResult class has a get() method that we use to retrieve values because we can’t reference any fields on an AggregateResult through dot notation
What is the FORMAT function?
the FORMAT function applies formatting corresponding to a user’s locale to standard/custom number, date, time, and currency fields
What is the Date function?
date functions allow us to filter or group results by specified dates
Child-to-Parent Queries
- to retrieve a field from a parent record, we use dot notation
- if we’re querying a custom relationship, the __c suffix on the relationship field in the query will change __r
- if we’re querying a standard relationship, these tend to follow the naming format ParentObjectId and we’ll lop off the Id at the end of the field API name (e.g. Account.Name)
List cons = [SELECT Id, Account.Name FROM Contact];List ties = [SELECT Id, Star_Destroyer__r.Name FROM Tie_Fighter__c];
Parent-to-Child Queries
- this is another SOQL query with its own SELECT and FROM clauses that lives in the SELECT clause of the outer query
- in a subquery, the name of the child object after the FROM clause is pluralized
- for standard relationships, this generally just means adding an s (e.g. Contacts)
- for custom relationships, the suffix of the object name will change from __c to __r as well
What are SOQL for loops used for?
SOQL for loops are used to avoid governor limits on heap size and the number of queries executed
