Q. How to fetch all field in SOQL Query?
In Spring 21, Salesforce is bringing a function through which we can fetch all fields in SOQL Query.
We have now FIELDS(ALL), FIELDS(STANDARD), and FIELDS(CUSTOM) which we can use in the SOQL query.
- FIELDS(ALL) – This fetches all the fields of an object. This is similar like Select * from SQL.
- FIELDS(STANDARD) – This fetches all standard fields of an object.
- FIELDS(CUSTOM) – This fetches all custom fields of an object.
Here is how the query will look like –
List<Contact> allConatcts = [SELECT FIELDS(ALL) FROM CONTACT LIMIT 200];
In Subqueries, it will look like –
List<Account> allAccounts = [
SELECT Account.Name, (SELECT FIELDS(ALL) FROM Account.Contacts LIMIT 200)
FROM Account
];
69. What is bounded and unbounded query?
When API cannot determine the set of fields in advance, then it will consider query as Unbounded Query.
if API can determine the set of fields in advance, then it is Bounded Query.
For example – The number of custom fields for an object is not pre-determined, so FIELDS(CUSTOM) and FIELDS(ALL) are considered as Unbounded Query.
Q. What is Query Plan tool?
Query Plan in the developer console can help to speed up SOQL queries done over large volumes of data. This is such a powerful tool when dealing with bad performance. Developer can use this tool to optimize and speed up SOQL queries.
Q. How to prevent SOQL injections?
There are a number of techniques you can use to prevent SOQL injection:
Static queries with bind variables
String.escapeSingleQuotes()
Type casting
Replacing characters
Whitelisting
Q. What Are The Types of SOQL Statements in SalesForce?
There are 2 types of SOQL Statements:
- Static SOQL
- Dynamic SOQL
Q. What is Static SOQL ?
Static SOQL statement is written in [ ] array brackets.
These statements are similar to LINQ
For Example :
string searchfor = ‘Sfdcamplified’ ;
Contact[] Contacts = [SELECT xyz__c, Firstname, Lastname FROM Contact WHERE Lastname = : searchfor];
Q. What is dynamic SOQL?
Dynamic SOQL refers to the creation of a SOQL string at runtime with Apex code. Dynamic SOQL enables you to create more flexible applications. For example, you can create a search based on input from an end user, or update records with varying field names. This is the major difference between soql and dynamic soql.
Dynamic SOQL :
– It is used to refer to the collection of a SOQL string at runtime with the apex code.
– Dynamic SOQL enables you to create a more flexible application.
– To create a Dynamic SOQL query at runtime use Database.Query() method, in one of the following ways.
– It returns a single sObject when the query returns a single record.
– For Example : sObject s = Database.query(string_limit_1)
– It returns a list of sObject when a query returns more than a single record.
For Example :
string myTestString = ‘TestName’ ;
List<sObject> sl = Database.query(SELECT Id,Name FROM myCustomObject__c WHERE Name=: myTestString);
Dynamic SOQL can be invoked by Database.query(query_string); where query_string is the query generated at runtime. In operation and processing, it works the same as Static SOQLs.
Example :
List<Contact> conList = Database.query(‘SELECT Id, Name
FROM contact
WHERE firstname= \’James\’ ‘);
Q. What is disadvantage of Dynamic SOQL?
One DISADVANTAGE with DYNAMIC SOQL is it causes SOQL injection in where condition which fetching on the basis of some text. To avoid which we need to use String.escapeSingleQuotes. There is no possibility of these in Static SOQL.
Q. What are some key considerations of Dynamic Query?
- Any compile-time errors are not thrown while saving a dynamic query.
- You have to be extremely cautious to prevent runtime errors.
- Dynamic SOQL can also cause potential security threats through SOQL injection. A malicious user might intentionally pass some characters which can retrieve more SOQL results that intended. If you must use dynamic SOQL, use the escapeSingleQuotes method to sanitize user-supplied input. This method adds the escape character () to all single quotation marks in a string that is passed in from a user. The method ensures that all single quotation marks are treated as enclosing strings, instead of database commands.
Q. What are Dynamic SOSL?
With Dynamic SOSL, developer can create SOSL statement in runtime thus allowing to build more flexible applications.
Dynamic SOSL statement returns a list of lists of sObjects, where each list contains the search result for a particular sObject type. Results will be stored in the same order as the sObjects are specified in the dynamic SOSL query. Dynamic SOSL can’t be used where an inline SOSL can be used, such as in regular assignments and for loops.
Dynamic SOSL is also having the same governor limits as static SOSL statements.
Q.When do we use database.query()?
database.query allows you to make a dynamic SOQL query at runtime. You can build up a string and then use that as a query string at run time in the database.
query statement to make a SOQL call that is determined at run time.
The database.query method should be used in instances where you are wanting to do a dynamic runtime SOQL query for your code.
Q.when do we use database.getquerylocator()?
database.getQueryLocator returns a Query Locator that runs your selected SOQL query returning list that can be iterated over in batch apex or used for displaying large sets in VF (allowing things such as pagination).
QueryLocator Methods
The following are methods for QueryLocator. All are instance methods.
getQuery()
Returns the query used to instantiate the Database.QueryLocator object. This is useful when testing the start method.
iterator()
Returns a new instance of a query locator iterator.
The query locator can return upto 50 million records and should be used in instances where you want to bactha a high volume of data.
Q. What is the use of order by clause in soql?
A. Use the optional ORDER BY in a SELECT statement of a SOQL query to control the order of the query results, such as alphabetically beginning with z.
If records are null, you can use ORDER BY to display the empty records first or last.
You can use ORDER BY in a SELECT statement to control the order of the query results. There is no guarantee of the order of results unless you use an ORDER BY clause
in a query.
Syntax: [ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ]
Q. What is OFFSET in soql?
A. Offset is used to skip the rows from the start of the query.
offeset considerations:
1. the maximum limit of offeset is 2000 rows. requesting on offset greater than 2000 will result in a number-out-side-valid-range error.
2. offset can’t be used in subquery in the where clause.
Q. What is LIMIT in soql?
Limit clause is used to restrict the number of rows to be returned by the soql. Limit statement should be the last statement in soql query
syntax: [select id, name, industry from account limit 1]
Note:
if you want to use limit and offset together, we have to use limit followed by offset
Q. How many records can be fetched using SOQL?
A. we can retrieve up to 50000 records from SOQL in a single transaction.
If we want to Fetch more than 50000 records by SOQL, You should look at using Batch Apex to accomplish your goals.
You cannot retrieve more than 50,000 records your SOQL calls in a single context. However, with Batch Apex your logic will be processed in chunks of anywhere from 1 to 200 records in a batch.
Q. Syntax to fetch the records from recycle bin using soql?
A. SELECT Id, isDeleted FROM <Oblectname> WHERE isDeleted = true All ROWS – This will only return the deleted rows.
SELECT Id, isDeleted FROM <Oblectname> WHERE isArchived = true All ROWS – This will only return the archived rows.
SELECT Id, isDeleted FROM <Oblectname> All ROWS – This will return the deleted records, archived records and records that are neither deleted nor archived (data set identical to the one returned by a SOQL not using ALL ROWS) as well.
You can-not use ALL ROWS and FOR UPDATE together.
Q.Name some date functions in soql?
A. DATE and TIME keywords
TODAY:
YESTERDAY:
THIS_WEEK
NEXT_WEEK
LAST_WEEK
same as for month and year
LAST_N_WEEK : N
NEXT_N_WEEK : N
same as for month, days, year
DATE and TIME methods:
calender_month()
calender_quarter()
calender_year()
Day_month()
Day_week()
Day_year()
Day_Only()
Q. What is FOR UPDATE clause in soql?
A. For Update clause will lock the records from getting updated from other transactions untill the current transaction is completed.
syntax: [select id, name from account for update]
Q. What is LIKE clause in soql?
A. like clause is used to compare the string values or used to search for the string value in the given fields
‘%’ – any no of characters
‘_’ – one character
syntax: [select lastname, firstname, phone from contat where lastname LIKE ‘%s’]
Q.How to use Datetime in Dynamic SOQL Query in Salesforce ?
//format the datetime to make it Dynamic Soql ready
String formatedDt = cutOffDateTime.format(‘yyyy-MM-dd’T’HH:mm:ss’Z”);
String sql = ‘SELECT Id FROM Covid_Answer__c WHERE covid_Test_Result__r.Patient_Name__r.IsActive__c = false AND LastModifiedDate < ‘+ formatedDt ;
Q. How you can use Datetime field as a criteria in SOQL Query ?
Ans :
We cannot use Datetime as condition in Where Clause in between single Quotes.
You can do something like this ,
WHERE CreatedDate > 2005-10-08T00:00:00Z
Or, you can also use Date Literals like
WHERE CreatedDate > YESTERDAY
Q. Opportunity record is having the currency field named “Opportunity Amount”. We need to fetch the Opportunity Amount based on the user’s currency mentioned in their user record.?
In order to fetch the Opportunity Amount based on the user’s currency, we can use Format() and convertCurrency() functions in SOQL.
Format(): This function can be used in SOQL select statement to format date, time, and currency fields based on the given user locale.
convertCurrency(): This function can also be used in SOQL select statement to convert the currency fields to the user’s currency.
Using the above two functions here is the SOQL statement:
SELECT Name, CurrencyIsoCode, Amount,FORMAT(convertCurrency(amount)) ConvertedinUserCurrency
FROM Opportunity WHERE Name = ‘Burlington Textiles Weaving Plant Generator’
Q.Select id from Account ALL ROWS. What is result of the query?
SOQL statements can use the ALL ROWS keywords to query all records in an organization, including deleted records and archived activities.
- System.assertEquals(3, [SELECT COUNT() FROM Contact WHERE AccountId = a.Id ALL ROWS]);
You can use ALL ROWS to query records in your organization’s Recycle Bin. You cannot use the ALL ROWS keywords with the FOR UPDATE keywords.
Q.What is difference between where and having?
- The WHERE clause filters records in a SOQL query that has no aggregate function.
- The HAVING clause filters the results after data is aggregated by an aggregate function.
Q.What is difference between SOSL and SOQL?
| SOQL (Salesforce Object Query Language) | SOSL (Salesforce Object Search Language) |
| Only one object can be searched at a time | Many objects can be searched at a time |
| Can query any type of field | Can query only on email, text or phone |
| Can be used in classes and triggers | Can be used in classes, but not triggers |
| DML Operation can be performed on query results | DML Operation cannot be performed on search results |
| Returns records | Returns fields |
Q. Explain different operators used in SOQL ?
| AND | Use AND to return records that meet two conditions. This query returns all records that have the first name Stella and the last name Pavlova.
SELECT Name, Email |
| OR | Use OR to return records that meet one of two conditions. This query returns records with the last name James or the last name Barr.
SELECT Name, Email |
| IN | Use IN to return records that meet at least one of three or more conditions. The IN clause is commonly used to return the values of a picklist, or values from a LIST or SET. IN simplifies a query that would otherwise have many OR conditions. This query returns all records that have the last name James, Barr, Nedaerk, or Forbes.
SELECT Name, Email FROM Contact |
| ASC | Returns results in ascending order | SELECT Name, Email FROM Contact ORDER BY Name ASC LIMIT 5 |
| DESC | Returns results in descending order | SELECT Name, Email FROM Contact ORDER BY Email DESC LIMIT 5 |
| NULLS
FIRST | LAST |
Returns null records at the beginning (NULLS FIRST) or end (NULLS LAST) | SELECT Name, Email FROM Contact ORDER BY Email NULLS LAST |
Q. Explain parent to child and child to parent query?
Child to Parent Query
In a child-to-parent query, you query the child object and get fields from a parent object by using dot notation, like this:
Eg: SELECT Name, Account.Name FROM Contact
Parent to Child Query
In a parent-to-child query, we use a subquery to get fields from the child object. A subquery is a SELECT statement enclosed in parentheses and nested within another query.
Within a subquery, instead of the related object’s field name, we use the child relationship name
The Query Results window displays account names and a collection of child contact records associated with each account. This type of collection probably looks familiar because we discussed maps
Q. Write Sample Query for Custom Objects for Child to Parent?
Broker__r is the custom relationship name.
SELECT Address__c, Picture__c, Broker__r.Name FROM Property__c
Q. Write Sample Query for Custom Objects for Parent to Child?
The Child Relationship Name is Properties. Because this is a custom relationship, when we use it in a query, we append __r (Properties__r). So our subquery (in parentheses) is:
SELECT Name, (SELECT Address__c, Price__c FROM Properties__r) FROM Broker__c
Q. We want only accounts that have a related contact with the last name Forbes. How Query works?
SELECT Name, (SELECT Name FROM Contacts) FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName = ‘Forbes’)
-
- The query finds contacts with the last name Forbes and returns the value of each contact’s AccountId field. (The WHERE clause subquery does that.)
- Next, it finds the value of that AccountId in the Id field of an account and gets the name for that account. (The main WHERE clause does that.)
- Then the query looks at the account’s related contacts, and gets the name of each contact. (The main query’s subquery does that.)
- Finally, the main query returns the name of each account that has a related contact with the last name Forbes, and for each of those accounts, the names of all related contacts.
Q. What is Bind Variable?
A bind variable is an Apex variable that you use in a SOQL query. Use bind variables as placeholders for specific values to be provided later.
Integer maxHomeValue = 200000;
List<Property__c> property = [SELECT Name, Price__c FROM Property__c WHERE Price__c < :maxHomeValue];
query, the bind variable is preceded by a colon ( : ).
Using bind variables in the WHERE clause allows you to set variable values in your code before the query runs. Bind variables make a query dynamic. To customize a query for each user, set bind variable values based on user input.
Q. What are Aggregate Functions?
| Aggregate Function | Description | Example |
| COUNT() | Returns the number of rows that are associated with the field | SELECT COUNT(Name)
FROM Broker__c |
| COUNT_DISTINCT() | Returns the number of unique rows that match the query criteria | SELECT COUNT_DISTINCT(City__c)
FROM Property__c |
| MIN() | Returns the minimum value of a field | SELECT MIN(Days_On_Market__c)
FROM Property__c |
| MAX() | Returns the maximum value of a field | SELECT MAX(Beds__c)
FROM Property__c |
| AVG() | Returns the average value of a numeric field | SELECT City__c, AVG(Days_On_Market__c)
FROM Property__c GROUP BY City__c |
| SUM() | Returns the total value of a numeric field | SELECT SUM(Price__c), Broker__r.Name
FROM Property__c GROUP BY Broker__r.Name |
Q. Explain Group By?
SELECT MAX(Status__c), Broker__r.Name FROM Property__c GROUP BY Broker__r.Name
this query groups properties by broker. For each broker’s properties, we list the most advanced Status__c value. That’s the Status picklist value that is closest to the final picklist value. We want brokers who have sold properties, so we’ll narrow the results even more.
Q. Explain HAVING?
SELECT MAX(Status__c), Broker__r.Name FROM Property__c GROUP BY Broker__r.Name HAVING MAX(Status__c) = ‘Closed’
Using the HAVING clause narrows results to properties with the Closed status
-
- The WHERE clause filters records in a SOQL query that has no aggregate function.
- The HAVING clause filters the results after data is aggregated by an aggregate function.
Q. What will happen if you use queryMore in query which has aggregate function?
Queries including an aggregate function don’t support queryMore. A run-time exception occurs if you use a query containing an aggregate function that returns more than 2,000 rows in a for loop.
Q.What will happen if SOQL query is written in for loop to access Custom Metadata Candidate Response?
It will hit the governor limit. There is a governor limit that enforces a maximum number of SOQL queries.
When queries are placed inside a for loop, a query is executed on each iteration and governor limit is easily reached.
Q. SOQL For Loops Versus Standard SOQL Queries?
SOQL query sometimes returns so many sObjects that the limit on heap size is exceeded and an error occurs. To resolve, use a SOQL query for loop instead, since it can process multiple batches of records by using internal calls to query and queryMore.
Developers should always use a SOQL for loop to process query results that return many records, to avoid the limit on heap size.
Q. Explain SOQL For Loop ?
SOQL for loops can process records one at a time using a single sObject variable, or in batches of 200 sObjects at a time using an sObject list:
- The single sObject format executes the for loop’s <code_block> once per sObject record. Consequently, it is easy to understand and use, but is inefficient if you want to use data manipulation language (DML) statements within the for loop body. Each DML statement ends up processing only one sObject at a time.
- The sObject list format executes the for loop’s <code_block> once per list of 200 sObjects.It is a little more difficult to understand and use, but is the optimal choice if you need to use DML statements within the for loop body. Each DML statement can bulk process a list of sObjects at a time.
Q. What is polymorphic relationship?
A polymorphic relationship is a relationship between objects where a referenced object can be one of several different types. For example, the Who relationship field of a Task can be a Contact or a Lead.
Approach 1: Filter results using the Type qualifier.
Example: Events that are related to an Account or Opportunity via the What field.
- List<Event> events = [SELECT Description FROM Event WHERE What.Type IN (‘Account’, ‘Opportunity’)]
Approach 2: Use the TYPEOF clause in the SOQL SELECT statement.
Example : Query Events that are related to an Account or Opportunity via the What field.
- List<Event> events = [SELECT TYPEOF What WHEN Account THEN Phone WHEN Opportunity THEN Amount END FROM Event];
These queries return a list of sObjects where the relationship field references the desired object types.
Q. How to do Typecasting Database.query List Result To Map?
String soqlQuery = ‘Select Id, Name From Account’;
Map <Id, Account> accountMap = new Map<Id, Account>(
(List<Account>)Database.query(soqlQuery)
);
System.debug(‘AccountMap-‘ + accountMap);
Reference
