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.

Advertisement

  • – 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:

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.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

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]

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 ;

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 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
FROM Contact
WHERE FirstName = ‘Stella’ AND LastName = ‘Pavlova’

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
FROM Contact
WHERE LastName = ‘James’ OR LastName = ‘Barr’

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
WHERE LastName IN (‘James’, ‘Barr’, ‘Nedaerk’, ‘Forbes’)

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’)

  1. 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.)
  2. 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.)
  3. Then the query looks at the account’s related contacts, and gets the name of each contact. (The main query’s subquery does that.)
  4. 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?

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

  1. The WHERE clause filters records in a SOQL query that has no aggregate function.
  2. 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

SOQL Scenarios in Apex

Please click here to read the original article as posted on Sfdcamplified.

We source the web to bring you best Salesforce articles for our reader’s convenience. If you want to have this article removed, please follow guidelines at Digital Millennium Copyright Act (DMCA)..