All About SOQL & SOSL

Salesforce Object Query Language (SOQL): Use this  Salesforce 
Object Query Language (SOQL) to construct simple but powerful query
strings in the queryString parameter in the query() call, in Apex 
statements, in Visualforce controllers and getter methods, or in 
the Schema Explorer of the Force.com IDE.

Salesforce Object Query Language (SOQL):
There are two types of SOQL statements.

  1. Static SOQL
  2. Dynamic SOQL.

Static SOQL: These type of SOQL statements are written in []’Square brackets’
Example:

String srchstring=’Manager’;
List orgs=[SELECT SELECT Id, Name
FROM Org__c WHERE Name =: srchstring];

 Dynamic SOQL: This type of SOQL queries are used to generate SOQL string at run time,We use Database.query() method.
Example 1:

String srchstring=’Manager’;
List<Org__c> orgs=Database.query(‘SELECT SELECT Id,
 Name FROM Org__c WHERE Name =: srchstring’);

 Example 2:

String srchstring=’Manager’;
List<Org__c> orgs=Database.query(‘SELECT SELECT Id,
Name FROM Org__c WHERE Name =’+ srchstring);

SOQL SELECT Syntax:

SELECT fieldList
 [TYPEOF typeOfField whenExpression elseExpression END]
 FROM objectType
 [WHERE conditionExpression]
 [GROUP BY fieldGroupByList]]
 [HAVING havingConditionExpression]
 [ORDER BY fieldOrderByList ASC | DESC ? NULLS FIRST | LAST ?]
(Sorting is case insensitive)
 [LIMIT ?]( Use LIMIT to specify the maximum
number of rows to return)
 [OFFSET ?]( Use OFFSET to specify the starting
row offset into the result set returned by your query)
 [UPDATE VIEWSTAT ?]

Note: 

  1. TYPEOF is currently available as a Developer Preview as part of the SOQL Polymorphism feature. For more information on enabling TYPEOF for your organization, contact salesforce.com.

  2. You can’t use a LIMIT clause in a query that uses an aggregate function, but does not use a GROUP BY

  3. The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 will result in a NUMBER_OUTSIDE_VALID_RANGE error.

  4. OFFSET is intended to be used in a top-level query, and is not allowed in most sub-queries, so the following query is invalid and will return a MALFORMED_QUERY error:

  5. OFFSET cannot be used as a sub-query in the WHERE clause, even if the parent query uses LIMIT

 SOQL Operators and its Description:

Operator    Description
LIKE Expression is true if the value in the specified fieldName matches the characters of the text string in the specified value. The LIKE operator in SOQL and SOSLis similar to the LIKE operator in SQL; it provides a mechanism for matching partial text strings and includes support for wildcards.

  • The % and _ wildcards are supported for the LIKE operator.
  • The % wildcard matches zero or more characters.
  • The _ wildcard matches exactly one character.
  • The text string in the specified value must be enclosed in single quotes.
  • The LIKE operator is supported for string fields only.
  • The LIKE operator performs a case-insensitive match, unlike the case-sensitive matching in SQL.
  • The LIKE operator in SOQL and SOSL supports escaping of special characters % or _.
  • Do not use the backslash character in a search except to escape a special character.

For example, the following query matches Appleton, Apple, and Appl, but not Bappl:

SELECT Id, Name FROM Org__c WHERE Name LIKE 'Huma%'
IN If the value equals any one of the specified values in a WHERE clause. For example:

SELECT Name FROM Org__c WHERE Name IN ('H.R', 'Manager')

Note that the values for IN must be in parentheses. String values must be surrounded by single quotes.

IN and NOT IN can also be used for semi-joins and anti-joins when querying on

ID (primary key) or reference (foreign key) fields.

NOT IN If the value does not equal any of the specified values in a WHERE clause. For example:

SELECT Name FROM Org__c WHERE Name NOT IN ('H.R', 'Manager')

Note that the values for NOT IN must be in parentheses, and string values must be surrounded by single quotes. There is also a logical operator NOT, which is unrelated to this comparison operator.

Basic limits:

  • No more than two IN or NOT IN statements per WHERE
  • You cannot use the NOT operator as a conjunction with semi-joins and anti-joins. Using them converts a semi-join to an anti-join, and vice versa. Instead of using the NOT operator, write the query in the appropriate semi-join or anti-join form.
INCLUDES EXCLUDES
Applies only to multi-select picklists.

SELECT Id, MSP1__c FROM CustObj__c
WHERE MSP1__c INCLUDES('AAA;BBB','CCC');

the query filters on values in the MSP1__c field that contains either of these values:

  • AAA and BBB selected.
  • CCC selected.

Main query limits:

The following restrictions apply to the main WHERE clause of a semi-join or anti-join query:

  1. The left operand must query a single ID (primary key) or reference (foreign key) field. The selected field in a subquery can be a reference field
    For example:

    SELECT Id
    FROM Idea
    WHERE (Id IN (SELECT ParentId FROM Vote WHERE CreatedDate >
    LAST_WEEK AND Parent.Type='Idea') 
  2. The left operand can’t use relationships.

Subquery limits:

  • A subquery must query a field referencing the same object type as the main query.
  • There is no limit on the number of records matched in a subquery. Standard SOQL query limits apply to the main query.
  • The selected column in a subquery must be a foreign key field, and cannot traverse relationships. This means that you cannot use dot notation in a selected field of a subquery. For example, the following query is valid:
SELECT Id, Name FROM Org__c
WHERE Id IN
(
  SELECT Org__c
  FROM Emp__c
  WHERE Name LIKE 'Manag%'
)
  • You cannot nest a semi-join or anti-join statement in another semi-join or anti-join statement.
  • You can use semi-joins and anti-joins in the main WHERE statement, but not in a subquery WHERE
  • You cannot use subqueries in conjunction with OR.
  • COUNT, FOR UPDATE, ORDER BY, and LIMIT are not supported in subqueries.
  • The following objects are not currently supported in subqueries:
    • ActivityHistory
    • Attachments
    • Event
    • EventAttendee
    • Note
    • OpenActivity
    • Tags (AccountTag, ContactTag, and all other tag objects)
    • Task

Date Literals: A fieldExpression can use a date literal to compare a range of values to the value in a date or dateTime field. Each literal is a range of time beginning with midnight (12:00:00). To find a value within the range, use =. To find values on either side of the range, use > or <.
Example:

SELECT Id FROM Org__c  WHERE CreatedDate=YESTERDAY 

(Starts 12:00:00 the day before and continues for 24 hours.)

Date Literal formats/types

YESTERDAY, TODAY, TOMORROW, LAST_WEEK, THIS_WEEK, NEXT_WEEK, LAST_MONTH, THIS_MONTH, NEXT_MONTH, LAST_90_DAYS, NEXT_90_DAYS,LAST_N_DAYS:n, NEXT_N_DAYS:n, NEXT_N_WEEKS:n

Example:

"SELECT Id FROM Org__c WHERE CreatedDate = LAST_N_DAYS:365" 
LAST_N_WEEKS:n, NEXT_N_MONTHS:n, LAST_N_MONTHS:n, THIS_QUARTER, LAST_QUARTER, NEXT_QUARTER, NEXT_N_QUARTERS:n, LAST_N_QUARTERS:n, THIS_YEAR LAST_N_MONTHS:n, THIS_QUARTER, LAST_QUARTER, NEXT_QUARTER, NEXT_N_QUARTERS:n, LAST_N_QUARTERS:n,THIS_YEAR, LAST_YEAR,NEXT_YEAR, NEXT_N_YEARS:n, LAST_N_YEARS:n 

Example:

“SELECT Id FROM Org__c WHERE LastModifiedDate > LAST_N_YEARS:5”
THIS_FISCAL_QUARTER, LAST_FISCAL_QUARTER, NEXT_FISCAL_QUARTE, NEXT_N_FISCAL_QUARTERS:n, LAST_N_FISCAL_QUARTERS:n,THIS_FISCAL_YEAR, LAST_FISCAL_YEAR, NEXT_FISCAL_YEAR, NEXT_N_FISCAL_YEARS:n, LAST_N_FISCAL_YEARS:n

Example:

“SELECT Id FROM Org__c WHERE LastModifiedDate >LAST_N_FISCAL_YEARS:3” 

GROUP BY:Use GROUP BY without, or with aggregate functions, such as SUM() or MAX(), to summarize the data and enable you to roll up query results.

SYNTAX: [GROUP BY fieldGroupByList]

This is valid:

SELECT Name, MAX(Amount), MIN(Amount) min, SUM(Amount) tot
FROM Opportunity
GROUP BY Name. 

Here min & tot is an alias for Amount field.

GROUP BY and Subtotals: To calculate subtotals for every possible combination of grouped field to generate a cross-tabular report.

GROUP BY ROLLUP:Use GROUP BY ROLLUP with aggregate functions, such as SUM() and COUNT(fieldName).

  • A query with a GROUP BY ROLLUP clause returns the same aggregated data as an equivalent query with a GROUP BY It also returns multiple levels of subtotal rows. You can include up to three fields in a comma-separated list in a GROUP BY ROLLUP clause.
    • First-level subtotals for each combination of fieldName1 and fieldName2. Results are grouped by fieldName3.
    • Second-level subtotals for each value of fieldName1.
    • Results are grouped by fieldName2 and fieldName3. One grand total row.
  • You can’t combine GROUP BY and GROUP BY ROLLUP syntax in the same statement. For example, GROUP BY ROLLUP(field1), field2 is not valid as all grouped fields must be within the parentheses.
  • If you want to compile a cross-tabular report including subtotals for every possible combination of fields in a GROUP BY clause, use GROUP BY CUBE

Example: rolls the results up by two fields.

SELECT Status, LeadSource, COUNT(Name) cnt
FROM Lead
GROUP BY ROLLUP(Status, LeadSource)

 GROUPING(fieldName) to Identify Subtotals: When you use GROUP BY ROLLUP or GROUP BY CUBE to add subtotals to your query results, you can use the GROUPING(fieldName) function to identify if a row is a subtotal for a field.
Example:

SELECT LeadSource, Rating,
GROUPING(LeadSource) grpLS, GROUPING(Rating) grpRating,
COUNT(Name) cnt
FROM Lead
GROUP BY ROLLUP(LeadSource, Rating) 

GROUP BY CUBE : Use GROUP BY CUBE to add subtotals for every possible combination of grouped field in the query results. This is particular useful if you need to compile cross-tabular reports of your data. Use GROUP BY CUBE with aggregate functions, such as SUM() and COUNT(fieldName).

The syntax is: [GROUP BY CUBE (fieldName1[, fieldName2, fieldName3])]

The following query returns subtotals of accounts for each combination of Type and BillingCountry:

SELECT Type, BillingCountry,
GROUPING(Type) grpType, GROUPING(BillingCountry) grpCty,
COUNT(id) accts
FROM Account
GROUP BY CUBE(Type, BillingCountry)
ORDER BY GROUPING(Type), GROUPING(BillingCountry)

HAVING :Use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, such as SUM(). A HAVING clause is similar to a WHERE clause. The difference is that you can include aggregate functions in a HAVING clause, but not in a WHERE clause.
The syntax is: [HAVING havingConditionExpression]

Example:

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource
HAVING COUNT(Name) > 100 

ORDER BY: Use ORDER BY in a SELECT statement to control the order of the query results.
The syntax is:[ORDER BY fieldExpression ASC | DESC ? NULLS FIRST | LAST ?]

Example:

SELECT Name
FROM Account
ORDER BY Name DESC NULLS LAST 
Limitation: These data types are not supported: multi-select 
picklist, rich text area, long text area, encrypted (if enabled), 
and data category group reference.
(If Salesforce Knowledge is enabled).

 LIMITUse LIMIT to specify the maximum number of rows to return:

Syntax: SELECT fieldList FROM objectType [WHERE conditionExpression] LIMIT number_of_rows

Example:

SELECT Name FROM Account
WHERE Industry = 'Media' LIMIT 125 

OFFSET: Use OFFSET to specify the starting row offset into the result set returned by your query. Using OFFSET is helpful for paging into large result sets, in scenarios where you need to quickly jump to a particular subset of the entire results.

Syntax:
SELECT fieldList FROM objectType [WHERE conditionExpression] ORDER BY fieldOrderByList LIMIT number_of_rows_to_return OFFSET number_of_rows_to_skip

Example:

SELECT Name
FROM Merchandise__c
WHERE Price__c > 5.0
ORDER BY Name
LIMIT 100
OFFSET 10<strong> 

Points to consider:

  • The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 will result in a NUMBER_OUTSIDE_VALID_RANGE
  • OFFSET is intended to be used in a top-level query, and is not allowed in most sub-queries, so the following query is invalid and will return a MALFORMED_QUERY error:
    SELECT Name, Id FROM Merchandise__c
    WHERE Id IN
    (
      SELECT Id
      FROM Discontinued_Merchandise__c
      LIMIT 100 OFFSET 20
    ) ORDER BY Name 
  • A sub-query can use OFFSET only if the parent query has a LIMIT 1 The following query is a valid use of OFFSET in a sub-query:
SELECT Name, Id
(
  SELECT Name FROM Opportunities LIMIT 10 OFFSET 2
)
FROM Account
ORDER BY Name LIMIT 1 
  • OFFSET cannot be used as a sub-query in the WHERE clause, even if the parent query uses LIMIT 1.

COUNT() and COUNT(fieldName): To discover the number of rows that are returned by a query, use COUNT() in a SELECT clause. There are two versions of
Syntax for COUNT():

COUNT()
COUNT(fieldName)

If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT().

 COUNT(): Returns the number of rows that match the filtering conditions.
Example(s):

  1. SELECT COUNT()FROM AccountWHERE Name LIKE 'a%' 
  1. SELECT COUNT() FROM Contact, Contact.Account
    WHERE Account.Name = 'MyriadPubs' 

For COUNT(), the query result size field returns the number of rows. The records field returns null.
Note the following when using COUNT():

  • COUNT() must be the only element in the SELECT list.

  • You can use COUNT() with a LIMIT clause.

  • You can’t use COUNT() with an ORDER BY clause. Use COUNT(fieldName) instead.

  • You can’t use COUNT() with a GROUP BY clause for API version 19.0 and later. Use COUNT(fieldName) instead.

COUNT(fieldName): Returns the number of rows that match the filtering conditions and have a non-null value for fieldName.

This syntax is newer than COUNT() and is available in API version 18.0 and later.

Example:

SELECT COUNT(Id)
FROM Account WHERE Name LIKE 'a%' 

COUNT(Id) returns the same count as COUNT(), so the previous and next queries are equivalent:

SELECT COUNT()
FROM Account WHERE Name LIKE 'a%' 
 Note: COUNT(Id) in SOQL is equivalent to COUNT(*) in SQL.

For COUNT(fieldName), the AggregateResult object in the records field returns the number of rows. The size field does not reflect the count. For example:

SELECT COUNT(Id)
FROM Account
WHERE Name LIKE 'a%' 

For this query, the count is returned in the expr0 field of the AggregateResult object. For more information, see Using Aliases with GROUP BY.
There are advantages to using COUNT(fieldName) instead of COUNT(). You can include multiple COUNT(fieldName) items in a SELECT clause. For example, the following query returns the number of opportunities, as well as the number of opportunities associated with a campaign. Note that the COUNT_DISTINCT() function returns the number of distinct non-null field values matching the query criteria.

Understanding Relationship Names

Understanding Relationship Names

Understanding Relationship Names

Parent –to-child (Std Object)SOQL query:
Example(s):

  1. SELECT Name,Phone, (SELECT Name, Phone FROM Contacts)
    FROM Account. 
  1. SELECT Name,Phone,
    (SELECT Name, Phone FROM Contacts),
    (SELECT Status, Stage FROM Opportunities)
    FROM Account. 

Parent –to-child (Cust Object)SOQL query:

SELECT Name, ( SELECT Name FROM custobjA<strong>__r</strong>)
FROM custobjB__c 

 Child – to-Parent (std Object)SOQL query:

SELECT Contact.FirstName,Contact.Phone,
Account.Name,Account.Phone FROM Contact. 

Child –to-Parent (cuts Object)SOQL query:

  1. SELECT Id, Name, Org__r.Name
    FROM emp__c
    WHERE Org__r.CreatedBy.LastName LIKE 'Muj%' 

 Understanding Relationship Query Limitations
When designing relationship queries, consider these limitations:

  • Relationship queries are not the same as SQL joins. You must have a relationship between objects to create a join in SOQL.
  • No more than 35 child-to-parent relationships can be specified in a query. A custom object allows up to 25 relationships, so you can reference all the child-to-parent relationships for a custom object in one query.
  • No more than 20 parent-to-child relationships can be specified in a query.
  • In each specified relationship, no more than five levels can be specified in a child-to-parent relationship. For example, Account.Owner.FirstName (three levels).
  • In each specified relationship, only one level of parent-to-child relationship can be specified in a query. For example, if the FROM clause specifies Account, the SELECT clause can only specify the Contact or other objects at that level. It could not specify a child object of Contact.

SOQL indexes:
Primary keys (Id, Name and Owner fields)
Foreign keys (lookup or master-detail relationship fields)
Audit dates (such as LastModifiedDate)
Custom fields marked as External ID or Unique.

Fields that can’t be indexed in SOQL:
Multi-select picklists
Currency fields in a multicurrency organization
Long text fields
Some formula fields
Binary fields (fields of type blob, file, or encrypted text.)
Note: the new data types, typically complex ones, may be added to Salesforce and fields of these types may not allow custom indexing.

SOQL injection:

“Use the escapeSingleQuotes method to avoid SOQL and SOSL injection attacks. “

    The most popular injection attacks occur when the user’s input is directly involved with the query or command. Therefore, the attacker can pass an untrusted date to execute a particular functionality or command. Then the attacker will get the access to unauthorized data.

    Apex uses SOQL as the query language and it has limited functionality than SQL. But the SOQL injection attacks are similar to SQL injection attacks. The Salesforce. com users are willing to put their sensitive data into Salesforce because Salesforce. com is a secure platform. Therefore, when we build custom pages and custom controllers, we must pay more attention to prevent such attacks. In Force.com, SOQL injections occur with dynamic SOQL queries.

    Dynamic SOQL is used to create the SOQL query string at the runtime of Apex code and allows us to build more flexible functionality (for example, the search functionality which depends on the user’s input). Using the Database. query(queryString) method, we can create dynamic queries that return a single sObject or a list of sObjects. The SOQL injection can be implemented in Apex if the application proceeds with the user’s input to build a dynamic SOQL and we haven’t handled the input properly.

   The Force.com platform provides a method called escapeSingleQuotes to prevent SOQL injections. Using this method, we can handle the user’s input by adding the escape character (\) to all single quotations in the user input string. Basically, this method considers all the single quotation as enclosing strings instead of database commands.

    The following example illustrates the SOQL injection’s vulnerability in Apex. This query returns order records which are not delivered and the customer’s name (cusName) for the specific order is found according to the user input.

String queryString = 'SELECT Id FROM Order__c
WHERE
(Delivered__c = false And
Customer__r.Name like \'%' + cusName + '%')'; 

If the user input is Muju, the executing query string would be as follows:

queryString = SELECT Id FROM Order__c
WHERE
(Delivered__c = false And
Customer__r.Name like '% Muju %') 

That’s a clean input.
But the problem is that users can enter malicious inputs, for example,

Muju% ' or Customer__r.Name like '. 

Then the query string would look as follows:

queryString = SELECT Id FROM Order__c
WHERE (Delivered__c = false And
Customer__r.Name like '%Muju%') or (Name like '%')

In this case, the result of the query will not return selective orders but will deliver all the orders from the database. This is the impact of SOQL injections. There is a way to protect from such SOQL injection attacks. We can use a string variable to assign the user input to, and add that particular variable to the dynamic query. The following is the fixed code snippet for the preceding vulnerability:

 String userInput = '%' + cusName + '%' 

 

String queryString = 'SELECT Id FROM Order__c
WHERE (Delivered__c = false And
Customer__r.Name like: userInput)'; 

SOSL:

SOSL statements evaluate to a list of lists of sObjects, where each list contains the search results for a particular sObject type. The result lists are always returned in the same order as they were specified in the SOSL query. If a SOSL query does not return any records for a specified sObject type, the search results include an empty list for that sObject. For example, you can return a list of accounts, contacts, opportunities, and leads that begin with the phrase map:

SOSL Syntax:
The SOSL query syntax consists of a required FIND clause followed by one or more optional clauses in the following order:

FIND {SearchQuery} [ IN SearchGroup [ convertCurrency(Amount)] ]
 [ RETURNING FieldSpec ]
 [ LIMIT n ] 
 [ UPDATE TRACKING ]
 [ UPDATE VIEWSTAT ]
 [ UPDATE TRACKING ]
 [ UPDATE VIEWSTAT ]

Example:

List<List<SObject>> searchList = [FIND 'map*' IN ALL FIELDS
RETURNING Account (id, name), Contact,Opportunity, Lead];
Account [] accounts = ((List<Account>)searchList[0]); 
Contact [] contacts = ((List<Contact>)searchList[1]);
Opportunity [] opporties = ((List<Opportunity>)searchList[2]);
Lead [] leads = ((List<Lead>)searchList[3]); 
SOSL statements evaluates to a list of lists of Sobjects, where each list contains the search for a particular Sobjecttype. The result list of lists are always returned in the same ordered as they quired.

 

Syntax Description
FIND
{SearchQuery}
Required. Specifies the text (words or phrases) to search for.
The search query must be delimited with curly braces.
IN
SearchGroup
Optional. Scope of fields to search. One of the following values:§  ALL FIELDS§  NAME FIELDS§  EMAIL FIELDS§  PHONE FIELDSIf unspecified, then the default is ALL FIELDS. You can specify the list of
objects to search in the RETURNING FieldSpec clause.

Note:  This clause doesn't apply to feed comments, feed items,
 and files. If any of these objects are specified in the RETURNING clause, 
the search is not limited to specific fields; all fields are returned.
convertCurrency
(Amount)
Optional. If an organization is multicurrency enabled,
converts currency fields to the user’s currency.
RETURNING
FieldSpec
Optional. Information to return in the search result. List of one or more objects and, within each object, list of one or more fields, with optional values to filter against. If unspecified, then the search results contain the IDs of all objects found.
LIMIT n Optional. Specifies the maximum number of rows returned in the text query, up to 2,000. If unspecified, the default is 2,000, the largest number of rows that can be returned. These limits apply to API version 28.0 and later. Previous versions support a maximum of 200 rows returned.
UPDATE
TRACKING
Optional. If an organization uses Salesforce Knowledge,
tracks keywords used in Salesforce Knowledge article search.
UPDATE
VIEWSTAT
Optional. If an organization uses Salesforce Knowledge,
updates an article’s view statistics.
Note: The SOSL statement character limit is tied to the SOQL 
statement character limit defined for your organization 
By default, SOQL and SOSL queries cannot exceed 20,000 
characters. For SOSL statements that exceed this maximum 
length, the API returns a  MALFORMED_SEARCH exception code; 
no result rows are returned.

FIND Clauses in Apex

The syntax of the FIND clause in Apex differs from the syntax of the FIND clause in the SOAP API:

  • In Apex, the value of the FIND clause is demarcated with single quotes.
    For example:
FIND ‘map*’ IN ALL FIELDS RETURNING Emp__c
(Id, Name,Designation__c), Org__c(Name)
  • In the Force.com API, the value of the FIND clause is demarcated with braces.
    For example:
FIND {map*} IN ALL FIELDS RETURNING Emp__c
(Id, Name,Designation__c), Org__c(Name) 

The Database.com Apex Code Developer’s Guide has more information about using SOSL and SOQL with Apex.

RETURNING FieldSpec

The optional RETURNING clause allows you to specify the information that is returned in the text search result. If unspecified, then the default behavior is to return the IDs of all objects that are searchable up to the maximum specified in the LIMIT n clause or 2,000
Syntax:

RETURNING ObjectTypeName
[(FieldList [WHERE conditionExpression] [ORDER BY clause] 
[LIMIT n] [OFFSET n])][, ObjectTypeName [(FieldList) 
[WHERE conditionExpression] [ORDER BY clause] 
[LIMIT n][OFFSET n])]] 

Designing Efficient Text Searches

If your searches are too general, they will be slow and return too many results. Use the following to write more efficient searches:

  • IN clause—for limiting the types of columns to search
  • RETURNING clause—for limiting the objects to search
  • LIMIT clause—for restricting the search results
  • OFFSET clause—for paging the search results

Dynamic SOSL:
It is used to create a query string at runtime.
Syntax:

Search.query(SOSL_querystring);
List<List<Sobject> searchresult=Search.query(soql_query_string);

 Example:

String soql_query_string=’FIND\’Ronald*\’
IN ALL FIELDS RETURNING Account(Id,Name),
Contact(Phone,email),Lead’;
List<List<Sobject>> searchresult=Search.query(soql_query_string);

 

Use SOQL When:

  • You know in which objects or fields the data resides.

  • You want to retrieve data from a single object or from multiple object
    that are related to one another.

  • You want to count the number of records that meet specified criteria.

  • You want to sort results as part of the query.

  • You want to retrieve data from number, date, or checkbox fields.

Use SOSL 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 using the divisions feature, and you want to find it in the most efficient way possible.

Limits
SOQL and SOSL generally have the same limitations, however according to the Governor Limit documentation:

**Description** **Limit**
Total number of SOQL queries issued 100
Total number of SOQL queries issued for Batch Apex and future methods 200
Total number of records retrieved by SOQL queries 50,000
Total number of records retrieved by Database.getQueryLocator 10,000
Total number of SOSL queries issued 20
Total number of records retrieved by a single SOSL query 200

Difference between SOSL and SOQL in Salesforce

Like Salesforce Object Query Language (SOQL), SOSL allows you to search your organization’s Database.com data for specific information. Unlike SOQL, which can only query one object at a time, a single SOSL query can search all objects—including custom objects—to which you have access. The API executes the search within the specified scope and returns to you only the information that is available to you based on the user permissions under which your application has logged in.

  •  Use SOQL with the query() call to select records for a single object.
  • Use SOSL with the search() call to find records for one or more objects. The search() call searches most text fields on an object.
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 used in Triggers. Can only be used in Apex class and anonymous block. Can be used in Apex class and Triggers.
returns fields returns records
Records are pertaining to different Sobjects. Records are stored in collection, records are pertaining to single Sobject.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s