Wednesday 29 August 2012

SOQL and SOSL Queries - Apex

The same way database systems support a query language for data retrieval, the Force.com peristence layer also provides two query languages.
  • 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. You'll see an example in a minute.
  • Salesforce Object Search Language (SOSL) is a simple language for searching across all multiple persisted objects simultaneously. SOSL is similar to Apache Lucene.
You can write queries directly in Apex without much additional code since Apex is tightly integrated with the database.

SOQL Query Examples

SOQL query is enclosed between square brackets. This example retrieves an sObject (a record from the database) that has the name field value equal to ‘Pencils’:
sObject s = [SELECT Id, Name FROM Merchandise__c WHERE Name='Pencils'];
This next example retrieves all matching merchandise items, assuming that there are zero or more merchandise items, and assigns them to a list. It shows how you can include a variable in a SOQL query by preceding it with a colon (:).
String myName = 'Pencils';
Merchandise__c[] ms = [SELECT Id FROM Merchandise__c WHERE Name=:myName];
Execute the following code to retrieve the first matching merchandise item and assign its Total_Inventory__c field to a variable:
Double totalInventory = [SELECT Total_Inventory__c 
                         FROM Merchandise__c 
                         WHERE Name = 'Pencils'][0].Total_Inventory__c;
System.debug('Total inventory: ' + totalInventory);
This is what you’ll get in the output.
Total inventory: 1000.0

SOSL Query Example

SOSL statements evaluate to a list of lists of sObjects, where each list contains the search results for a particular sObject type. Here's an example that searches all field across all Merchandise__c and Inventory_Statement__c sObjects. Execute the following:
List<List<SObject>> searchList = [FIND 'Pencil*' IN ALL FIELDS RETURNING 
                                  Merchandise__c (Id, Name), Invoice_Statement__c];
Merchandise__c[] merList = ((List<Merchandise__c>)searchList[0]);
Invoice_Statement__c[] invList = ((List<Invoice_Statement__c>)searchList[1]);
System.debug('Found ' + merList.size() + ' merchandise items.');
System.debug('Found ' + invList.size() + ' invoice statements.');
You’ll get something similar to this in the output.
Found 1 merchandise items.
Found 0 invoice statements.

2 comments: