Introduction to SOQL
What
is SOQL?
What
are the SOQL features?
·
SOQL is case insensitive.
·
SOQL is very easy to
understand when compared with SQL.
·
SOQL helps to optimize code.
·
SOQL reduces our workload
and time.
·
Data from objects can be
extracted to Excel sheets using SOQL.
·
SOQL queries will be
required in APEX programming.
·
Wide knowledge in SOQL will
help to be a good developer.
SOQL
purpose
Salesforce
Object Query language in APEX statements. In Visualforce
Controllers and the getter methods. In Schema Explorer of the Force.com IDE.
Salesforce
Object Query Language (SOQL) is case insensitive which means we can write in
Uppercase, Lowercase and in both lower and uppercase. For our convenience in
our Salesforce Developer Tutorials we us uppercase for SOQL key words and
Lowercase letters for names, field’s lists and conditions.
When
SOQL is used we can optimize the code which results faster execution of
results. Data can be extracted from salesforce Custom objects and Standard
Objects with the help of Salesforce Object Query Language (SOQL).
In
SOQL we use API names of Standard and Custom Objects. Generally Custom objects
API name is always ends with __c.
How to know API
names of the Custom Objects.
In
Custom object Definition detail section we can observe API Name as shown above.
For custom object student the API name is Student__C.
SOQL
Syntax:
Salesforce
Object Query Language uses SELECT keyword to fetch data from Objects and
fields. In Structures Query language
(SQL) the keyword SELECT is used to fetch data.
SOQL Syntax:
SELECT fields
FROM object
WHERE condition ordering LIMIT FOR VIEW Or FOR REFERENCE
OFFSET
UPDATE VIEWSTAT
From above
syntax we have to know clearly about each term in the Syntax. Remember
carefully stated below.
Field: When we mention fields it uses
API names of an Object.
Object: When we mention Object it
denotes Custom and Standard Objects.
Condition: To
filter records we use conditions in SOQL.
Ordering: This is used
for ordering the results. This may be optional in SOQL.
Limit: It limits the number of records
when fetching.
FOR VIEW: This is used to
fetch records from last day fetched records.
FOR REFERENCE: To
update LastReferencedDate this is used.
OFFSET: To
denote the starting row of the record this is used.
UPDATE VIEWSTA: This updates
the articles view statistics for fetched records.
When writing
SOQL statements we use API names for fields and labels of the fields should not
be used.
Standard
Objects:
Field Name refers as API name.
Custom
Objects:
API name column refers to API name.
How to get API
names for Standard Objects?
In this example
we are going to get API name for Standard Object called “Account”. Follow the
steps as shown below.
Go to
Build=>Customize=>Accounts=>Fields.
In Standard
Object Field name as referred as API name as shown below.
How to get API
names for Custom Objects?
To know API
names for Custom objects in salesforce go to Build=>Create=>Objects.
The API names of
the fields are as shown above.
How to Write
SOQL Statements?
In this example
we are taking Standard Object called “Account“. Account is a standard object
where we store information about our customers and partners.
SELECT id, Name FROM
Account.
From above SOQL
statement we fetching Id and name of the Standard object called “Account”.
We can fetch
both Custom fields and Standards fields in standard objects.
SELECT name, id, Active__c FROM Account.
From above
screenshot we have fetched both standard and custom fields from Account object.
Custom fields with extension __c.
SELECT name, s1__c, s2__c, s3__c, country__c FROM student__c.
From
above example student is the custom object so we have given name as student__c and the custom fields called
s1__c, s2__c, s3__c and country__c. Name field is the Standard field in above example.
In
Salesforce.com every object has system fields or standard fields. These system
fields have read-only property.
List
of system fields are.
Id
IsDeleted
CreatedById.
CreatedDate.
LastModifiedById.
LastModifiedDate.
SystemModstamp.
Let
us see another example to fetch data called first name and last name from
standard object called User.
SELECT Firstname,
Lastname FROM User
These are the
few examples of SOQL statement queries. From this Salesforce tutorial we have learned
Basic SOQL Statements. We have seen fetching records from Standard objects and
Custom object.
Alias Notations
in SOQL |Basic SOQL Statements
Alias notation
in SOQL statements are used to distinguish different object used in a single
SOQL Statement. They are different types SOQL reserved keywords that cannot be
used in alias names or alias notations in SOQL. Some of the alias name is.
1. AND.
2. ASC
3. DESC.
4. EXCLUDES.
5. FIRST.
6. FROM.
7. GROUP.
8. HAVING.
9. IN.
10. INCLUDES.
11. LAST.
12. LIKE.
13. LIMIT.
14. NOT.
15. NULL.
16. NULLS.
17. OR.
18. SELECT.
19. WHERE.
20. WITH
Let us see an
example to understand alias notations in SOQL.
SELECT Acct.Id, Acct.name FROM Account Acct
From
above SOQL statement we fetching Account Id, Account name records from the
Standard object called Account. we have mentioned the word “ Acct”.
“Acct” is the alias name for “Account”. We can fetch data
directly without using alis name in order to understand alias names in SOQL we
are using alias names in this example.
§ SELECT Firstname,lastname FROM ContactCon,Con.Account
Acct WHERE Acct.name=‘Ashok‘
Alias Notations in SOQL|Basic SOQL
Statements
From above SOQL
Statement Acct is the alias name for “Account” Con is the alias name for
“Contact”. We are fetching first name and last name from two objects called
Contacts and Account. In both contacts and account we have first name and last
name. Where is the condition to get the details from specific record called
“Ashok”.
From above SOQL
statement we fetch last name and first name from the Account and Contacts of
Ashok. These alias names are helpful when writing SOQL queries for multiple
objects. When writing SOQL statements for multiple objects many fields are
common in all the objects so the alias names are helpful to distinguish the
difference between the objects.
What is WHERE clause in SOQL?
WHERE
clause is also called as Conditional Expression. WHERE clause is used
to filter the retrieved data.
Whenever
if we want to filter data from a set of object records we use WHERE clause in
SOQL. This WHERE clause filters the data based on the given Condition or
Criteria. Basically when WHERE clause in SOQL statement is used WHERE clause is
followed by some comparison operators and Logical Operators like IN operator, NOT
IN operator, INCLUDES operator, EXCLUDES Operator.
Let us see an
example on WHERE clause.
§ SELECT firstname,
lastname FROM Contact WHERE firstname
!= null
Where clause in SOQL
From
above SOQL statement we are fetching data From Standard object called “Contact”.
We are using conditional expression “WHERE” to filter the
retrieved data from Contact object. Proceeding WHERE we
have used! = operator to filter the data firstname which is not
null.
Let us see another
example to understand WHERE clause in SOQL.
§ SELECT Firstname,
Lastname FROM Contact WHERE lastname
= ‘Adarsh’
Where clause in SOQL
From
SOQL Statement we fetching data from Contact whose first name is “Adarsh”. In
SOQL statements text words are mentioned with ‘ ‘.
In our next
salesforce tutorial we are going to learn about the comparsion operators in
salesforce SOQL statements.
Comparison
Operators in SOQL
Comparison operators in SOQL are used to compare a value with
another value to return TRUE or FALSE. Different types of comparison operators in SOQL
(Salesforce Object Query Language) are
|
Operator
|
Description
|
|
= |
Equals |
|
!= |
Not Equals |
|
< |
Less than |
|
<= |
Less than or equal to |
|
> |
Greater than |
|
>= |
Greater than or equal to |
|
LIKE |
Like |
Equals Operator in SOQL
Let us see an example using Equals operator to implement SOQL
statements. Using Equal Operator in SOQL can retrieve the data based on
criteria and condition equals “=”.
§ SELECT Firstname, Lastname FROM Contact WHERE firstname = ‘Adarsh‘
From above SOQL statement
the equals ” = ” checks whether the values of the two operands are equal. If
the value is equal, the condition becomes ” TRUE ” . If not the condition
Fails.
Not Equals Operator.
Let us see an example using Not equals operator to implement
SOQL Statements. Using Not Equals ” != “ data can retrieved based on the
condition or Criteria.
§ SELECT firstname, lastname FROM Contact WHERE firstname != ‘adarsh‘
From the above SOQL
statement the date is retrieved from all the contacts whose first name not
equals to Adarsh.
Less than or Equal to Operator in SOQL
Let us see an example on
Less than or Equal to operator in SOQL. using this operator we can retrieve records
that are less than or equal to the given limit.
§ SELECT name, Fee_paid__c FROM student__c WHERE fee_paid__c <= 4000.
Less than or equal
to operator is used to check whether the values of the left operand is
less than or equal to the value of the right operand. If the condition
satisfies the condition will be TRUE.
From above SOQL statement
the students who paid fee less than or equals to 40000 will be filtered.
Less than Operator in SOQL.
Comparison
Operators in SOQL
Greater than or Equal to operator in SOQL
Greater than Operator in SOQL.
LIKE operator in SOQL
Comparison
Operators in SOQL
Like operator is used to retrieve the data from the matched
substring provided. LIKE operator is also called as pattern matching filtering
technique. We have to use ” % ” character to include names ending with “express“
SOQL IN Operator
In Salesforce Object Query
Language IN Operator is used to specify multiple values in the WHERE clause for
matching and filtering records.
When we use IN operator in
SOQL the data is fetched from the matched values specified in the SOQL
statement.
Let us see an example on
SOQL IN Operator.
§ SELECT Firstname, Lastname FROM Contact WHERE Firstname IN ( ‘Rose’, ‘Sean’ )
SOQL IN
Operator
Data is retrieved from the
records whose first name is Rose and Sean. Values inside the braces are case
insensitive.
SOQL NOT IN Operator
SOQL NOT IN operator is
used to specify multiple values in the WHERE clause for un-matching and
filtering records. This operator is used to fetch data which are not matched
with the values specified in the statement.
§ SELECT firstname, lastname FROM Contact WHERE firstname NOT IN ( ‘Rose’, ‘Sean’ )
SOQL IN
Operator
In this example we have
used NOT IN Operator by using this operator the data is fetched other than Rose
and Sean from contacts.
What is a SOQL Logical Operator?
SOQL Logic Operators are the connectors for connecting one or more conditions in a
Single SOQL statement. These SOQL
logical operators are
very easy to use to check multiple conditions in a single Salesforce Object
Query language statement.
There are two types of SOQL Logical operators in salesforce.com. They are
1.
Operator AND.
2.
Operator OR.
SOQL Logical Operator AND
Logical Operator “AND” is
used to retrieve the records when all the conditions in SOQL statement is
satisfied. If any of the condition in SOQL statement is not satisfied
records will not be retrieved.
§ SELECT name, Amount FROM opportunity WHERE Amount > 1000 AND Amount < 10000
SOQL Logical Operator OR
Logical operator OR is used
to retrieve the data if any of the condition in SOQL statement is satisfied.
SOQL logical operator “OR” matches the first condition with second condition
and data will be retrieved.
SELECT name, Amount FROM opportunity WHERE name = ‘express logistics‘ OR name
= ‘GenePoint SLA’
What is
SOQL ORDER BY Clause?
ORDER BY Clause is used to retrieve the data in “Ascending” or “Descending”
order by the condition given in SOQL Statement.
In salesforce ORDER BY
clause are two types. They are
1.
ASC.
2.
DESC.
Here ASC means ascending
and DESC means descending order. In SOQL by default the data will be retrieved
in ascending order only.
SOQL ORDER BY Clause ASC
§ SELECT name, Amount FROM opportunity ORDER BY name ASC.
Here we have used ORDER BY Clause called ASC. From above SOQL statement
name and Amount values will be retrieved from Opportunity and records will be
arranged in Ascending order.
SOQL ORDER BY Clause DESC.
§ SELECT name, Amount FROM Opportunity ORDER BY name DESC.
Here we have used SOQL
ORDER BY clause called
“DESC”. From above SOQL statement name and Amount
values will be retrieved from opportunity in descending order.
SOQL Includes and excludes operators are mainly used to filter multipicklist field
values in salesforce. These operators are used for only multipicklist values.
Multipicklist values are those have more than one picklist values.
For example a
student must have many skills to get a job (salesforce, Java, C, C#, ORACLE
etc).
§ SELECT name,
country__c, Student_skills__c FROM Student__C .
SOQL Includes, Excludes Operators
From above SOQL
statement all the records from Student object has retrieved.
INCLUDES Operator
Includes operator
is used to filter and retrieve the data that contains any of the specified
values in SOQL statement.
§ SELECT name,
country__c, Student_skills__c FROM Student__C WHERE student_skills__CINCLUDES (‘salesforce’).
SOQL Includes, Excludes Operators
From
above SOQL INCLUDES operator statement we filtering the data whose student
skills is equal to‘salesforce’.
Excludes Operator
§ SELECT name, Country__C, Student_skills FROM Student__c WHERE student_skillsEXCLUDES (‘salesforce’)
SOQL Includes, Excludes Operators
From above SOQL
statement we fetching the data whose student will have no salesforce skills. Condition
must be written in single quotations and braces as shown above.
SOQL Relationships between objects are mainly used to query the records from one or
more objects in a single SOQL statement in salesforce.com.
Different
SOQL Relationships.
1.
SOQL Relationships between Standard objects.
2.
SOQL Relationships between custom objects.
3.
Many-to-one relationships: (n:1)
SOQL Relationships between Standard objects.
Parent-to-Child Relationship.
1.
When a relationship is from parent-to-child then SOQL statement must have sub Query in
its Statement.
2.
For standard objects SOQL relationships name is equal to plural of the Child Object name.
§ SELECT name, (SELECT Lastname FROM CONTACTS) FROM Account.
Child-to-Parent
Relationship.
1.
When a SOQL Relationship is from Child-to-Parent then the SOQL statement will not have
Sub Query.
2.
SOQL Relationships name is equal to name of the parent object.
§ SELECT contact. Firstname, contact.Account.name FROM Contact.
Many-to-one relationships: (n:1)
Child-to-Parent relationship
These relationships are
specified in SELECT, FROM, WHERE clauses using dot(.) operator.
§ SELECT id, Name,
Account.name FROM Contact WHERE Account.Industry = ‘Media’.
Parent-to-Child Relationship
§ SELECT name (SELECT lastname FROM Contacts) FROM Account.
SOQL Relationships between custom objects.
Parent-to-Child relationship.
§ SELECT Lastname__c, (SELECT Lastname__c FROM Daughters__r
) FROM Mother__c.
Child-to-Parent relationship.
§ SELECT Id, Firstname__c, Mother__r.firstname__c FROM Daughter__c WHEREMother__r.Lastname__c
LIKE ‘c%’.
Advanced SOQL Statements:
What is an escape sequence?
An escape sequence
character is a character that invokes an alternative interpretation of the
subsequent characters in a character sequence.
Some of the escape sequence
characters are as follows.
1. \n or \N – New line.
2. \r or \R – Carriage return.
3. \t or \T – Tab.
4. \f or \F – Form feed.
5. \b or\B – Bell.
6. \” –
One double-quote character.
7. \’ –
One single-quote character.
8. \\ –
Backslash.
9. The LIKE operator expression: \_ –
It matches a single underscore character (_).
10. The LIKE operator expression: \% –
Matches s single percentage a sign character (%).
SOQL Date Formats
When SOQL
statements we should follow some date formats which are predefined in salesforce.
There are four types of date formats in Salesforce object Query language. They
are.
|
DATE
FORMAT |
EXAMPLES |
|
YYYY-MM-DD |
2014-06-28 |
|
YYYY-MM-DD Thh:mm:ss+hh:mm |
2014-06-28 T23:01:01+01:00 |
|
YYYY-MM-DD Thh:mm:ss-hh:mm |
2014-06-28 T23:01:01+01-08:00 |
|
YYYY-MM-DD Thh:mm:ssZ |
2014-06-28 T23:01:01Z |
SOQL Date literals.
There are different
types of SOQL Date literals are present when querying the records using the
date fields in the SOQL statements the date literals can be used. Some of the
Date literals are as given below.
1. YESTERDAY.
2. TODAY.
3. TOMORROW.
4. LAST_WEEK.
5. NEXT_WEEK.
6. LAST_MONTH.
7. THIS_MONTH.
8. NEXT_MONTH.
9. LAST_90_DAYS.
10. NEXT_90_DAYS.
11. LAST_N_DAYS:n.
12. NEXT_N_DAYS:n.
13. THIS_QUARTER.
14. NEXT_QUARTER.
15. NEXT_N_QUARTER:n.
16. LAST_N_QUARTERS:n.
17. THIS_YEAR.
18. LAST_YEAR.
19. NEXT_YEAR.
20. NEXT_N_YEARS:n.
21. LAST_N_YEARS:n.
22. THIS_FISCAL_QUARTER.
23. LAST_FISCAL_QUARTER.
24. NEXT_FISCAL_QUARTER.
25. NEXT_N_FISCAL_QUARTERS:n.
26. LAST_N_FISCAL_QUARTERS:n.
27. THIS_FISCAL_YEAR.
28. LAST_FISCAL_YEAR.
29. NEXT_FISCAL_YEAR.
30. NEXT_N_FISCAL_YEAR:n.
31. LAST_N_FISCAL_YEARS:n.
What is SOQL GROUP BY ROLLUP Clause?
SOQL GROUP BY ROLLUP Clause is used to add subtotals to get aggregates data in the query
results. Both GROUP BY and GROUP BY ROLLUP Clause are similar but SOQL GROUP
BY ROLLUP Clause returns multiple levels of subtotal rows. When using GROUP
BY ROLLUP Clause in a statement we can add up to three fields in a
comma-separated list.
§ SELECT city_c, State_c, COUNT(Employee_Name__C) Counts FROM Employee__C GROUP BY ROLLUP (City__c,
State__C).
SOQL GROUP
BY ROLLUP Clause
From above SOQL statement
Grouped the data according to State, city and also w calculates the number.
SOQL FOR REFERENCE Clause
This SOQL FOR REFERENCE
clause is used to find Date or Time values when a record has been referenced.
The LastReferencedDate field is automatically updated for any retrieved
records.
When coming to FOR
REFERENCE Clause, this is used to tack Date an Time of the record when a record
has been referenced last while executing a SOQL query.
§ SELECT city__c, state__c, lastreferenceddate FROM Employee__C FOR
REFERENCE
When we execute the above
SOQL statement all the records will show the same date and time.
SOQL FOR VIEW Clause.
FOR VIEW Clause is used to
find the date of the record when it has been last viewed. Every record will
have a standard field called LastViewedDate. This field will be updated for
every retrieved records.
FOR VIEW clause is used to
track the date when the records was viewed last while executing a SOQL Query.
§ SELECT City_c, State_c, lastViewedDate FROM Employee__C FOR VIEW
When the above statement is
executed for first time it shows the last viewed date and when the SOQL
statement is executed for second time all the records will show the same date
and time for lastVieweddate.
SOQL Functions
§ Group By Class: Group By Class is used to divide total number of data in to
groups based on criteria. It allows to classify the data in to groups based on
criteria.
§ Having Class: Having Class is used to specify the search condition.
SOQL Group By Clause.
In SOQL Group functions are
used to group set of records by the values specified in the filed. This
function will gather all the data in the specified fields and also allow to
perform aggregated functions.
§ SELECT Continent__c, Country__c, Avg ( Average__c ) FROM Student__c Group ByContinent__c, Country__c.
List of
Aggregated functions in Group Function.
1.
COUNT ( )
2.
COUNT ( FIELD_NAME )
3.
COUNT_DISTINCT ( )
4.
SUM ( )
5.
MIN ( )
6.
MAX ( )
Above aggregated functions
are used in Group By clause to fetch data from salesforce objects.
COUNT ( ) Method.
COUNT ( ) method is used to
find the total number of records in an object that matches the specified
condition.
§ SELECT COUNT ( ) FROM Student__c.
COUNT (FIELD_NAME ) Method.
COUNT (FIELD_NAME ) Method
is used to the total number of records of a particular value in the specified
value.
§ SELECT City__c, COUNT ( Employee_name__c ) FROM Employee__C Group By City__C.
COUNT_DISTINCT() Method.
COUNT_DISTINCT() Method is
used to find the total number of distinct non-null field values. This method
ignored the null values and returns the non-null values.
§ SELECT COUNT_DISTINCT (City__C), TotalCities FROM
Employee__c
MIN () Method
The MIN() method in SOQL is
used to return the lowest values.
§ SELECT MIN ( Average__c) FROM Student__C.
MAX () Method.
MAX () method is used to
return the maximum or the largest value.
§ SELECT MAX (Average__C) FROM Student__c.
HAVING Clause.
HAVING Clause is SOQL is
used to apply a condition based on a group condition. It is very similar to
WHERE clause.
§ Having Clause is
used for only aggregated functions.
§ It is used to
specify the search condition.
§ It limits the
grouped records.
Example: List the
students data Continent wise whose subject average is greater than 50.
SOQL Functions
, Group Functions
§ SELECT Avg(Average__c), Continent__c FROM Student__C Group By Continent__cHAVING Avg(Average__c) < 50
Inner
and Outer Join Relationships in Salesforce.
Earlier in our Salesforce Tutorial we have learned about Different Relationships in Salesforce.
Although Salesforce Object Query Language does not support or allow arbitrary
joints but it supports some features to control related objects in Salesforce.
In this SOQL Salesforce
Tutorial we learn about some additional SOQL features. Some of he additional
features are.
1.
Inner Join and Outer Join.
2.
Semi Join and Anti-Join.
3.
Multi Select pick lists.
Inner Join Relationships in Salesforce.
In SOQL statements, Inner
and Outer Joins are used to join related objects like parent object and child
objects to retrieve all related fields from base objects including the records
of non refer related object.
Let us see an example:-
§ SELECT NAME, ACCOUNT__r.NAME FROM
PROJ__C.
From above SOQL statement,
we are trying to retrieve Name and Account name from Project object(custom
object). Here the parent object is Account object and child object is project
and the relation between two objects is Look up Relationship. __r keyword is
added to custom objects for relationships in Salesforce.
SOQL statement consists of single base object and it is
specified using the keyword called “FROM”. Here we using Force.com IDE’s schema explorer to run SOQL statements. Above shown statement is Outer join
statement. Outer joins does not retrieve test project values because it does
not match to any row in the account object tables.
SOQL Inner joins Relationships in Salesforce.
SOQL Inner Join statements are used to eliminate the records which records are not matched
with related objects. In SOQL inner join statements we use filtering condition
as shown below.
Example: - SELECT NAME, ACCOUNT__r.NAME FROM PROJ__C
WHERE ACCOUNT_c !=NULL.
From above screenshot we
observe that the unmatched records are eliminated by filtering condition. Form
this article we have successfully learned about SOQL Inner Join,
Outer Join Relationships in Salesforce. In our next SOQL tutorial we learn
about Semi Join and Anti Join Relationships in Salesforce.
How Semi Join and Anti Join
Relationships in Salesforce are Helpful?
Semi
Join and Anti Join are used to to enhance the functionality of parent-to-child
queries and child-to-parent queries in Salesforce Object Query Language and
also they they are useful in create child-to-childrelationships.
These Semi Join and
Anti Join allows records to filter from one object to another object using sub
query.
Parent to Child relationships in Salesforce Example
§ Select Account.name, (Select contact.firstname,
contact.lastname FROM Account.Contacts )FROM Account.
In Parent-to-Child
Relationships name will be plural of the child object and it contains sub
query.
SOQL Semi Join example.
§ Select Id, Name From Account WHERE Id IN (Select
Account__c FROM Proj__c WHERE status__c = ‘Yellow’).
We have to read
above SOQL query from the bottom to understand. Sub query identifies all
projects which are in yellow status and also it returns their unique Id
and Name.
SOQL Anti Join Example
§ Select Id, Name FROM Account WHERE Id NOT IN
(Select Account__c FROM Proj__c WHERE status__c = ‘Green’).
Semi Join, Anti Join Relationships in
Salesforce
Anti Join is Purely
reverse to Semi Join. In Anti Join we use Key words like NOT, IN in sub queries
to exclude certain records from the parent object. From above SOQL Query we are
trying to retrieve all records from project object except the records which are
in yellow status and also we excluding the records which are not associated
with parent object.
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:
|
1 2 |
|
|
1 |
|
|
1 |
|
|
1 |
|
|
1 |
|
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 |
Required.
Specifies the text (words or phrases) to search for. |
|
IN |
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 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 |
Optional.
If an organization is multicurrency enabled, |
|
RETURNING |
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 |
Optional.
If an organization uses Salesforce Knowledge, |
|
UPDATE |
Optional.
If an organization uses Salesforce Knowledge, |
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:
|
1 2 |
|
§ In
the Force.com API, the value of the FIND clause is demarcated with braces.
For example:
|
1 2 |
|
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:
|
1 2 3 4 5 |
|
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);
|
1 |
|
Example:
|
1 2 3 |
|
|
|
1 |
|
|
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 OBJECTS
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. |
No comments:
Post a Comment