SOQL and SOSL

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 uses the SELECT statement combined with filtering statements to return sets of data, which may optionally be ordered:

  1. SELECT one or more fields 
  2. FROM an object 
  3. WHERE filter statements and, optionally, results are ordered
Salesforce Object Search Language (SOSL) is a simple language for searching across all multiple persisted objects simultaneously. SOSL is similar to Apache Lucene.

SOSL
SOQL
Stands for "Salesforce object search language".
Stands for "Salesforce object query language".
Works on multiple objects at a same time.
Need to write different SOQL for different object.
All fields are already text indexed.
SOQL against same field will be slow.
Cannot be used in Triggers. Can only be used in Apex class and anonymous block.
Can be used in Apex class and Triggers.



Quoted String Escape Sequences

You can use the following escape sequences with SOQL:
SequenceMeaning
\n or \NNew line
\r or \RCarriage return
\t or \TTab
\b or \BBell
\f or \FForm feed
\"One double-quote character
\'One single-quote character
\\Backslash
LIKE expression only: \_Matches a single underscore character ( _ )
LIKE expression only:\%Matches a single percent sign character ( % )
If you use a backslash character in any other context, an error occurs.

Escaped Character Examples

SELECT Id FROM Account WHERE Name LIKE 'Ter%'
Select all accounts whose name begins with the three character sequence 'Ter'.
SELECT Id FROM Account WHERE Name LIKE 'Ter\%'
Select all accounts whose name exactly matches the four character sequence 'Ter%'.
SELECT Id FROM Account WHERE Name LIKE 'Ter\%%'
Select all accounts whose name begins with the four character sequence 'Ter%'


Reserved Characters

 Reserved characters, if specified in aSELECT clause as a literal string (between single quotes), must be escaped (preceded by the backslash \ character) in order to be properly interpreted. An error occurs if you do not precede reserved characters with a backslash.
The following characters are reserved:
' (single quote)
\ (backslash)
For example, to query the Account  Name field for “Bob's BBQ,” use the following SELECT statement:
SELECT Id
FROM Account
WHERE Name LIKE 'Bob\'s BBQ'



Using Apex Variables in SOQL and SOSL Queries

Account A = new Account(Name='xxx');
insert A;
Account B;

// A simple bind
B = [SELECT Id FROM Account WHERE Id = :A.Id];

// A bind with arithmetic
B = [SELECT Id FROM Account 
     WHERE Name = :('x' + 'xx')];

String s = 'XXX';

// A bind with expressions
B = [SELECT Id FROM Account 
     WHERE Name = :'XXXX'.substring(0,3)];

// A bind with an expression that is itself a query result
B = [SELECT Id FROM Account
     WHERE Name = :[SELECT Name FROM Account
                    WHERE Id = :A.Id].Name];

Contact C = new Contact(LastName='xxx', AccountId=A.Id);
insert new Contact[]{C, new Contact(LastName='yyy', 
                                    accountId=A.id)};

// Binds in both the parent and aggregate queries
B = [SELECT Id, (SELECT Id FROM Contacts
                 WHERE Id = :C.Id)
     FROM Account
     WHERE Id = :A.Id];

// One contact returned
Contact D = B.Contacts;

// A limit bind
Integer i = 1;
B = [SELECT Id FROM Account LIMIT :i];

// An OFFSET bind
Integer offsetVal = 10;
List<Account> offsetList = [SELECT Id FROM Account OFFSET :offsetVal];

// An IN-bind with an Id list. Note that a list of sObjects
// can also be used--the Ids of the objects are used for 
// the bind
Contact[] cc = [SELECT Id FROM Contact LIMIT 2];
Task[] tt = [SELECT Id FROM Task WHERE WhoId IN :cc];

// An IN-bind with a String list
String[] ss = new String[]{'a', 'b'};
Account[] aa = [SELECT Id FROM Account 
                WHERE AccountNumber IN :ss];

// A SOSL query with binds in all possible clauses

String myString1 = 'aaa';
String myString2 = 'bbb';
Integer myInt3 = 11;
String myString4 = 'ccc';
Integer myInt5 = 22;

List<List<SObject>> searchList = [FIND :myString1 IN ALL FIELDS 
                                  RETURNING 
                                     Account (Id, Name WHERE Name LIKE :myString2
                                              LIMIT :myInt3), 
                                     Contact, 
                                     Opportunity, 
                                     Lead 
                                  WITH DIVISION =:myString4 
                                  LIMIT :myInt5];


SOQL Query Examples
  1. sObject s = [SELECT Id, Name FROM Merchandise__c WHERE Name='Pencils'];

  1. String myName = 'Pencils';
  2. Merchandise__c[ ] ms = [SELECT Id FROM Merchandise__c WHERE Name=:myName];


  1. Double totalInventory = [SELECT Total_Inventory__c FROM Merchandise__c WHERE Name = 'Pencils'][0].Total_Inventory__c;
  2. System.debug('Total inventory: ' + totalInventory);

Output  :  Total inventory: 1000.0

SOSL Query Example


  1. List<List<SObject>> searchList = [FIND 'Pencil*' IN ALL FIELDS RETURNING
  2. Merchandise__c (Id, Name), Invoice_Statement__c];
  3. Merchandise__c[] merList = ((List<Merchandise__c>)searchList[0]);
  4. Invoice_Statement__c[] invList = ((List<Invoice_Statement__c>)searchList[1]);
  5. System.debug('Found ' + merList.size() + ' merchandise items.');
  6. System.debug('Found ' + invList.size() + ' invoice statements.');
Output:
Found 1 merchandise items.
Found 0 invoice statements.

Choosing Between SOQL and SOSL

Problem
You know that the platform supports Salesforce.com Object Query Language (SOQL) and Salesforce.com Object Search Language (SOSL), but you don't know what the difference is between the two, or when to use one over the other.
Solution
A SOQL query is the equivalent of a SELECT clause in a SQL statement. Use SOQL with a query() call when:

  • You know in which objects or fields the data resides
  • You want to retrieve data from a single object or from multiple objects that are related to
  • one another
  • You want to count the number of records that meet particular criteria
  • You want to sort your results as part of the query
  • You want to retrieve data from number, date, or checkbox fields

A SOSL query is a programmatic way of performing a text-based search. Use SOSL with a
search() call when:

  • You don't know in which object or field the data resides and you want to find it in the most efficient way possible
  • You want to retrieve multiple objects and fields efficiently, and the objects may or may not be related to one another
  • You want to retrieve data for a particular division in an organization with Divisions, and you want to find it in the most efficient way possible

Tip: Although SOQL was previously the only one of the two query languages that allowed condition-based filtering with WHERE clauses, as of the Summer '07 release SOSL supports this functionality as well.


Querying Multiple Related Objects Using Relationship Queries


For each of the following examples, the child object is the object on which the relationship field (the foreign key) is defined, and the parent is the object that the child references:
Basic Child-to-Parent (Foreign Key) Traversal
To traverse a relationship from a child to a parent, use standard dot notation off the name of the relationship. For example, this SOQL query retrieves information about contacts from the Contact object, along with the name of each contact's related account (the parent object):

  1. SELECT Id, LastName, FirstName, Account.Name FROM Contact

Account is the name of the relationship that's defined by the AccountId lookup field on the Contact object. Using dot notation, this SOQL query retrieves the Name field on the account that is related through the Account relationship.
Expanded Child-to-Parent (Foreign Key) Traversal
Child-to-parent traversals can extend up to five levels from the original root object. For example, the last selected field in this SOQL statement extends two levels from the root contact record by retrieving the name of the parent account on the account associated with the contact:

  1. SELECT Id, LastName, FirstName, Account.Name, Account.Parent.Name FROM Contact

Basic Parent-to-Child (Aggregate) Traversal
To traverse a relationship from a parent to a set of children, use a nested query. For example, this SOQL query retrieves opportunities and the opportunity products associated with each opportunity:

  1. SELECT Id, Name, Amount, (SELECT Quantity, UnitPrice, TotalPrice FROM OpportunityLineItems) FROM Opportunity

Using the nested query, we're specifying that for each opportunity we want the respective set of OpportunityLineItem records that are related through the OpportunityLineItems child relationship.
Combined Child-to-Parent and Parent-to-Child Traversal
Foreign key and aggregate traversals can also be combined in a single query. For example:

  1. SELECT Id, Name, Account.Name, (SELECT Quantity, UnitPrice, TotalPrice, PricebookEntry.Name, PricebookEntry.Product2.Family FROM OpportunityLineItems) FROM Opportunity

sObject Relationships and Dot Notation

If two sObjects are related to each other via a relationship, you can get a parent sObject of an sObject using the dot notation syntax:

sObjectTypeName parentObject = objectA.RelationshipName;

You can also access the fields of the parent sObject by appending it to the relationship name:

DataType s = objectA.RelationshipName.FieldName;

Similarly, you can get the child sObjects of an sObject using the same syntax. The only difference is that you now have a collection of one or more sObject child records, while in the previous case there is only one parent record. The syntax is the following:

List<sObjectTypeName> children = objectA.ChildRelationshipName;

Querying sObject Relationships

If an sObject is related to another by a master-detail or lookup relationship, you can query the parent sObject field by specifying the relationship name and field name in your SELECT statement as follows:

SELECT RelationshipName.Field FROM sObjectName WHERE Where_Condition [...]

To fetch child sObjects, specify a nested query that retrieves all request child sObjects and their fields as follows:

SELECT field1, field1, ..., (Nested query for child sObject field FROM relation) FROM sObjectName WHERE Where_Condition [...]

Relationship Queries

Account is a parent of Contact
  1. SELECT Contact.FirstName, Contact.Account.Name from Contact

  1. SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account

Querying Multiple Related Objects Using Relationship Queries


For each of the following examples, the child object is the object on which the relationship field (the foreign key) is defined, and the parent is the object that the child references:
Basic Child-to-Parent (Foreign Key) Traversal
To traverse a relationship from a child to a parent, use standard dot notation off the name of the relationship. For example, this SOQL query retrieves information about contacts from the Contact object, along with the name of each contact's related account (the parent object):

  1. SELECT Id, LastName, FirstName, Account.Name FROM Contact

Account is the name of the relationship that's defined by the AccountId lookup field on the Contact object. Using dot notation, this SOQL query retrieves the Name field on the account that is related through the Account relationship.
Expanded Child-to-Parent (Foreign Key) Traversal
Child-to-parent traversals can extend up to five levels from the original root object. For example, the last selected field in this SOQL statement extends two levels from the root contact record by retrieving the name of the parent account on the account associated with the contact:

  1. SELECT Id, LastName, FirstName, Account.Name, Account.Parent.Name FROM Contact

Basic Parent-to-Child (Aggregate) Traversal
To traverse a relationship from a parent to a set of children, use a nested query. For example, this SOQL query retrieves opportunities and the opportunity products associated with each opportunity:

  1. SELECT Id, Name, Amount, (SELECT Quantity, UnitPrice, TotalPrice FROM OpportunityLineItems) FROM Opportunity
  1. SELECT Name, (select Id from Contacts) FROM Account

Using the nested query, we're specifying that for each opportunity we want the respective set of OpportunityLineItem records that are related through the OpportunityLineItems child relationship.
Combined Child-to-Parent and Parent-to-Child Traversal
Foreign key and aggregate traversals can also be combined in a single query. For example:

  1. SELECT Id, Name, Account.Name, (SELECT Quantity, UnitPrice, TotalPrice, PricebookEntry.Name, PricebookEntry.Product2.Family FROM OpportunityLineItems) FROM Opportunity

SOQL Statements


? -  In a syntax statement, the question mark indicates the element preceding it is optional. You may omit the element or include one.

Using alias notation in SELECT queries:

  1. SELECT count() FROM Contact c, c.Account a WHERE a.name = 'MyriadPubs'
In Developer Console to print the count of the Contacts in Account 'MyriadPubs' use 
  1. System.Debug([SELECT count() FROM Contact c, c.Account a WHERE a.name = 'Aashu']);

To retrieve a list of accounts that are named Acme:

  1. List<Account> aa = [SELECT Id, Name FROM Account WHERE Name = 'Acme'];

From this list, you can access individual elements:

  1. if (!aa.isEmpty()) {
  2.    // Execute commands
  3. }


To creates a new contact for the first account with the number of employees greater than 10:
  1. Contact c = new Contact(Account = [SELECT Name FROM Account WHERE NumberOfEmployees > 10 LIMIT 1]); 
  2. c.FirstName = 'James'; 
  3. c.LastName = 'Yoyce';

To count the total number of contacts with the last name of Weissman:
  1. Integer i = [SELECT COUNT() FROM Contact WHERE LastName = 'Weissman'];
To operate on the results using standard arithmetic:
  1. Integer j = 5 * [SELECT COUNT() FROM Account];
To restrict deleting a record related to another record in another object
  1. trigger RestrictInvoiceDeletion on Invoice_Statement__c (before delete) {
  2. // With each of the invoice statements targeted by the trigger
  3. // and that have line items, add an error to prevent them
  4. // from being deleted.
  5. for (Invoice_Statement__c invoice :
  6. [SELECT Id
  7. FROM Invoice_Statement__c
  8. WHERE Id IN (SELECT Invoice_Statement__c FROM Line_Item__c) AND
  9. Id IN :Trigger.old]){
  10. Trigger.oldMap.get(invoice.Id).addError('Cannot delete invoice statement with line items');
  11. }
  12. }



SOQL For Loops

This syntax can be used to loop through the sObjects returned by the query, one at a time, or in batches of 200 sObjects when using a list variable to hold the query results. Using a list variable to hold the query results in the SOQL for loop is a good way to query a large number of records since this helps avoid the heap limit, which is one of the governor execution limits.

In this example, each iteration of the for loop operates on a single sObject returned by the query. This is inefficient if you perform database operations inside the for loop because they execute once for each sObject and you’re more likely to reach certain governor limits.

for (Merchandise__c tmp : [SELECT Id FROM Merchandise__c]) {
// Perform some actions on the single merchandise record.
}

A more efficient way is to use a list variable to hold the batch of records returned by each iteration of the for loop. This allows for bulk processing of database operations. The following example uses a list variable in the for loop.

for (Merchandise__c[] tmp : [SELECT Id FROM Merchandise__c]) {
// Perform some actions on the single merchandise record.

}

Comments

Popular Posts