Know about clauses in SOQL query
SOQL queries has various pre-defined clause that can be used to filter the results. Here we can see the use of few clause that can be used
SCOPE
USING SCOPE clause of a SOQL query returns records within a specified scope. For example: You can limit of the record to return objects records that user owns.
Syntax:
[USING SCOPE filterScope]
One of the keyword can be specified in filterscope
Delegated
Filter for records delegated to another user for action. For example, a query could filter for only delegated Task records.
Everything
Filter for all records.
Mine
Filter for records owned by the user running the query.
My_Territory
Filter for records in the territory of the user running the query. This option is available if territory management is enabled for your organization.
My_Team_Territory
Filter for records in the territory of the team of the user running the query.
Note: Territory scope can be used only when the organization has territory management enabled.
Team
Filter for records assigned to a team, such as an Account team.
Example:
SELECT Name FROM Account USING SCOPE mine
SCOPE
USING SCOPE clause of a SOQL query returns records within a specified scope. For example: You can limit of the record to return objects records that user owns.
Syntax:
[USING SCOPE filterScope]
One of the keyword can be specified in filterscope
Delegated
Filter for records delegated to another user for action. For example, a query could filter for only delegated Task records.
Everything
Filter for all records.
Mine
Filter for records owned by the user running the query.
My_Territory
Filter for records in the territory of the user running the query. This option is available if territory management is enabled for your organization.
My_Team_Territory
Filter for records in the territory of the team of the user running the query.
Note: Territory scope can be used only when the organization has territory management enabled.
Team
Filter for records assigned to a team, such as an Account team.
Example:
SELECT Name FROM Account USING SCOPE mine
The above query returns only the records owned by the user running the query.
ORDER BY
Using ORDER BY clause is nothing but a conditional sorting to control the list of the records return by the SOQL query.
Syntax:
[ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ]
ASC or DESC - Specifies whether the results are ordered in ascending (ASC) or descending (DESC) order. Default order is ascending.
NULLS FIRST or NULLS LAST - Orders null records at the beginning (NULLS FIRST) or end (NULLS LAST) of the results. By default, null values are sorted first.
Example:
SELECT Name FROM Account ORDER BY Name DESC NULL LAST
The above query returns the record on alphabetical order of Name in descending order with NULL values returned in the last.
There are few restrictions that needs to be considered while using ORDER BY clause. Look the link for more details ORDER BY
Example:
SELECT Name FROM Account ORDER BY Name DESC NULL LAST
The above query returns the record on alphabetical order of Name in descending order with NULL values returned in the last.
There are few restrictions that needs to be considered while using ORDER BY clause. Look the link for more details ORDER BY
LIMIT
LIMIT clause is an optional clause which allows to specify the maximum number of records that a SOQL can return.
Syntax:
SELECT ***fieldList*** FROM objectType [WHERE conditionExpression] [LIMIT numberOfRows]
Example:
SELECT Name FROM Account LIMIT 100
The above query returns first 100 accounts.
OFFSET
When expecting many records in a query’s results, you can display the results in multiple pages by using the OFFSET clause on a SOQL query. For example, you can use OFFSET to display records 51–75 and then jump to displaying records 301–350. Using OFFSET is an efficient way to handle large results sets.
Use OFFSET to specify the starting row offset into the result set returned by your query. Because the offset calculation is done on the server and only the result subset is returned, using OFFSET is more efficient than retrieving the full result set and then filtering the results locally.
Syntax:
SELECT ***fieldList*** FROM objectType [WHERE conditionExpression] ORDER BY fieldOrderByList LIMIT numberOfRowsToReturn OFFSET numberOfRowsToSkip
Example:
SELECT Name FROM Opportunity WHERE Stage == 'Qualification' ORDER BY Name LIMIT 100 OFFSET 10
The above query returns result sets from 11 to 110 ordered by name which has stage set as Qualification.
LIMIT clause is an optional clause which allows to specify the maximum number of records that a SOQL can return.
Syntax:
SELECT ***fieldList*** FROM objectType [WHERE conditionExpression] [LIMIT numberOfRows]
Example:
SELECT Name FROM Account LIMIT 100
The above query returns first 100 accounts.
OFFSET
When expecting many records in a query’s results, you can display the results in multiple pages by using the OFFSET clause on a SOQL query. For example, you can use OFFSET to display records 51–75 and then jump to displaying records 301–350. Using OFFSET is an efficient way to handle large results sets.
Use OFFSET to specify the starting row offset into the result set returned by your query. Because the offset calculation is done on the server and only the result subset is returned, using OFFSET is more efficient than retrieving the full result set and then filtering the results locally.
Syntax:
SELECT ***fieldList*** FROM objectType [WHERE conditionExpression] ORDER BY fieldOrderByList LIMIT numberOfRowsToReturn OFFSET numberOfRowsToSkip
Example:
SELECT Name FROM Opportunity WHERE Stage == 'Qualification' ORDER BY Name LIMIT 100 OFFSET 10
The above query returns result sets from 11 to 110 ordered by name which has stage set as Qualification.
Comments
Post a Comment