Sunday, April 13, 2014

What is SOQL in Apex?

What is SOQL in Apex?

Salesforce Object Query Language (SOQL) is a query-only language. While similar to SQL in some ways, it's an object query language that uses relationships, not joins, for a more intuitive navigation of data. This is the main query language that is used for data retrieval of a single sOobject and its related sObjects. 

SOQL Query Examples
A SOQL query is enclosed between square brackets.

sObject s = [SELECT Id, Name FROM Merchandise__c WHERE Name='Pencils'];

A SOQL statement is centered on a single database object, specifying one or more fields to retrieve from it.The fields to select are separated by commas.

Simple SOQL Statement

SELECT Id, Name FROM Account

Filtering Records

SOQL supports filter conditions to reduce the number of records returned.A filter condition
consists of a field name to filter, an operator, and a literal value.
Valid operators are

> (greater than),
< (less than),
>= (greater than or equal to),
<= (less than or equal to),
= (equal to),
!= (not equal to),
IN and NOT IN (matches a list of literal values, and supports semi-joins and anti-joins), and INCLUDES and EXCLUDES (match against multi-select picklist values).

On String fields the LIKE operator is also available,
which applies a pattern to filter records.The pattern uses the % wildcard to match zero or
more characters, _ to match one character, and the \ character to escape the % and _
wildcards, treating them as regular characters.

EX: -

FROM Account
WHERE AnnualRevenue > 100000000
AND Type = 'Customer - Direct'
AND LastModifiedDate = THIS_YEAR

SOQL Statement with Record Limit

SELECT Name, Type
FROM Account
WHERE LastModifiedDate = TODAY

Sorting Query Results

Results of a query can be sorted by up to 32 fields in ascending (ASC, the default) or descending (DESC) order. Sorting is not case-sensitive, and nulls appear first unless otherwise specified (NULLS LAST).Multi-select picklists, long text areas, and reference type fields cannot be used as sort fields.

EX: -

SELECT Name, Type, AnnualRevenue
FROM Account
ORDER BY Type, LastModifiedDate DESC

Querying Multiple Objects

The result of a SOQL query can be a simple list of records containing rows and columns or hierarchies of records containing data from multiple, related objects. Relationships between objects are navigated implicitly from the database structure.

The two ways to navigate object relationships in SOQL are child-to-parent and parent-to-child.

SOQL with Child-to-Parent Relationship
EX: -

SELECT Name, Contact__r.MailingCity, Contact__r.CreatedBy.Name
FROM Resource__c
WHERE Contact__r.MailingState = 'IL'

At most, five levels of parent objects can be referenced in a single child-to-parent query, and the query cannot reference more than 25 relationships in total.

SOQL with Parent-to-Child Relationship

The second form of relationship query is the parent-to-child query.

EX: -

SELECT Id, Name,
(SELECT Total_Hours__c
FROM Timecards__r
WHERE Week_Ending__c = THIS_MONTH)
FROM Resource__c

Note: A parent-to-child query cannot reference more than twenty child objects.

SOQL Query in Apex Using SOQL For Loop

Decimal totalHours = 0;
for (Proj__c project : [ SELECT Total_Billable_Hours_Invoiced__c FROM Proj__c
WHERE Start_Date__c = THIS_YEAR ]) {
  totalHours += project.Total_Billable_Hours_Invoiced__c;


Post a Comment

| ,