In This post we will about Salesforce Object Query Language [soql]. We used soql to fetch data from object in salesforce.
SOQL ( Salesforce Object Query Language)
To read a record from Salesforce, you need to write a query. Salesforce provides the Salesforce Object Query Language or SOQL in short, that you can use to read saved records.
When SOQL is embedded in Apex, it is referred to as inline SOQL.
To include Salesforce Object Query Language queries within your Apex code, wrap the SOQL statement within square brackets and assign the return value to an array of sObjects.
Account[] accts = [ SELECT Name, Phone FROM Account];
Prerequisites
Some queries in this unit except the org to have accounts and contacts. Before you run the queries, create some sample data.
// Add account and related contact
Account acct = new Account(
Name=’SFDC Computing’,
Phone=’(415)555-1212’,
NumberOfEmployees=50,
BillingCity=’San Francisco’);
insert acct;
// Once the account is inserted, the sObject will be
// populated with an ID.
// Get this ID.
ID acctID = acct.ID;
// Add a contact to this account.
Contact con = new Contact(
FirstName=’Carol’,
LastName=’Ruiz’,
Phone=’(415)555-1212’,
Departement=’Wingo’,
AccountId=acctID);
insert con;
// Add account with no contact
Account acct2 = new Account(
Name=’The SFDC Query Man’,
Phone=’(310)555-1213’,
NumberOfEmployees=50,
BillingCity=’Los Angeles’,
Description=’Expert in wing technologies.’);
insert acct2;
To run SOQL query Using the Query editor
The Developer Console provides the Query Editor console, which enables you to run your SOQL queries and view results. The Query Editor provides a quick way to inspect the database. It is a good way to test your SOQL queries before adding them to your Apex code.
SELECT Name,Phone FROM Account
Basic SOQL Syntax
This is the syntax of a basic SOQL query;
SELECT fields FROM ObjectName [WHERE CONDITION]
The WHERE clause is optional.
For example, the following query retrieves accounts and gets two fields for each account: the ID and the phone.
SELECT Phone FROM Account;
Unlike other SQL languages, you can’t specify * for all fields. You must specify every field you want to get explicitly. If you try to access a field you haven’t specified in the SELECT clause, you’ll get an error because the field hasn’t been retrieved.
You don’t need to specify the Id field in the query as it is always returned in Apex queries, whether it is specified in the query or not.
For example:
SELECT Id,Phone FROM Account
and
SELECT Phone FROM Account
are equivalent statements.
The only time you may want to specify the Id field if it is the only field you’re retrieving because you have to list at least one field.
SELECT Id FROM Account
You may want to specify the Id filed also when running a query in the Query Editor as the ID field won’t displayed unless specified.
Filtering Query Results with Conditions
If you have more than one account in the org, they will all be returned. If you want to limit accounts returned to accounts that fulfil a certain condition, you can add this condition inside the WHERE clause.
SELECT Name,Phone FROM Account WHERE Name=’SFDC Computing’
SELECT Name,Phone FROM Account WHERE (Name=’SFDC Computing’ AND NumberOfEmployees>25)
SELECT Name,Phone FROM Account WHERE (Name=’SFDC Computing’ OR (NumberOfEmployees>25 AND BillingCity=’Los Angeles’))
For example, you can retrieve all accounts whose name start with SFDC bt using this condition: WHERE Name LIKE ‘SFDC%’. The % wildcard character matches any or no character. The _ character in contrast can be used to match just one character.
Ordering Query Results
When a query executes, it returns records from Salesforce in no particular order
SELECT Name,Phone FROM Account ORDER BY Name
The previous statement is equivalent to:
SELECT Name,Phone FROM Account ORDER BY Name ASC
To reverse the order, use the DESC keyword for descending order:
SELECT Name,Phone FROM Account ORDER BY Name DESC
You can short on most fields, including numeric and text fields. You can’t sort on fields like rich text and multi-select picklists.
Limiting the Number of Records Returned
You can limit the number of records returned to an arbitrary number by adding the LIMIT n clause where n is the number of records you want returned.
Account oneAccountOnly = [SELECT Name,Phone FROM Account LIMIT 1];
Combining All Pieces Together
You can combine the optional clauses in one query, in the following order:
SELECT Name,Phone FROM Account
WHERE (Name= ‘SFDC Computing’ AND NumberOfEmployees>25)
ORDER BY Name
LIMIT 10
Execute the following SOQL query in Apex by using the Execute Anonymous window in the Developer Console. Then inspect the debug statements in the debug log. One sample account should be returned.
Account[] accts = [SELECT Name, Phone FROM Account]
WHERE (Name=’SFDC Computing’ AND NumberOfEmployees>25)
ORDER BY Name
LIMIT 10];
System.debug(accts.size() + ‘account(s) returned.’);
// Write all account array info
System.debug(accts);
Accessing Variables in SOQL Queries
SOQL statements in Apex can reference Apex code variables and expressions if they are preceded by a colon (:). The use of a local variable within a SOQL statement is called a bind.
This example shows how to use the targetDepartment variable in the WHERE clause.
String taregtDepartment = ‘Sales’;
Contact[] techContacts = [SELECT FirstName,LastName
FROM Contact WHERE Department=:targetdepartment];
Querying Related Records
To get child records related to a parent record, add an inner query for the child records. The FROM clause of the inner query runs against the relationship name,rather than a Salesforce object name.
This example contains an inner query to get all contacts that are associated with each returned account.
SELECT Name, (SELECT LastName FROM Contacts) FROM Account WHERE Name = ‘SFDC Computing’
This next example embeds the example SOQL query in Apex and shows how to get the child records from the SOQL result by using the Contacts relationship name on the sObject.
Account[] acctsWithContacts = [SELECT Name,
(SELECT FirstName,LastName FROM Contacts)
FROM Account
WHERE Name = ‘SFDC Computing’];
// Get child records
Contacts[] cts = acctsWithContacts[0].Conatcts;
System.debug(‘Name of first associated contact: ‘
+ cts[0].FirstName + ‘,’ + cts[0].LastName);
You can traverse a relationship from a child object(contact) to a field on its parent (Account.Name) by using dot notation. For example, the following Apex snippet queries contact records whose first name is Carol and is able to retrieve the name of Carol’s associated account by traversing the relationship between accounts and contacts.
Contact[] cts = [SELECT Account.Name FROM Contact
WHERE FirstName = ‘Carol’ AND LastName=’Ruiz’];
Contact carol = cts[0];
String acctName = carol.Account.Name;
System.debug(‘Carol\’s account name is ‘ + acctName);
Querying Record in Batches By Using SOQL For Loops
With a SOQL for loop, you can include a SOQL query within a for loop. The results of a SOQL query can be iterated over within the loop.
SOQL for loops iterate over all of the sObject records returned by a SOQL query. The syntax of a SOQL for loop is either:
for (variable : [soql_query]) {
code_block
}
Or
for (variable_list : [soql_query]) {
code_block
}
NOTE : Both variable and variable_list must be of the same type as the sObject that are returned by the soql_query.
Hits: 549