query
Executes a Salesforce Object Query Language (SOQL) query. The results of the query are delivered into the variable context as a one or more Data items describing the records.
Please note that this command does not support all SOQL features, such as subqueries. The query's result size is also limited to 2000 records at most, so please split the query into ones with more constrained result sets when needing data of several thousands of records.
While the query command is well-suited for getting various kinds of data from your organization, one thing it does not do well is retrieval of file data. You can, for example, query the VersionData field of a ContentVersion record, but what you'll receive is the file data as a base64-encoded String. Commands requiring File values do not understand base64-encoded Strings, so values of that kind are pretty much useless. To get the file data of a ContentVersion and other similar objects, please use the load command instead as it will retrieve the data and properly construct it into a File value that other commands can handle.
Attributes
var | ||
|---|---|---|
Required | Value type | EL-evaluated |
Yes | String | No |
Defines the name of the variable holding the query result. The value of the variable is either a Data item or a Collection of Data items, depending on whether the query returns results for one or several records. If the query returns no records, the value will be an empty Data item. | ||
select | ||
|---|---|---|
Required | Value type | EL-evaluated |
Yes | String | Yes |
Defines the SOQL query expression to execute. | ||
Restrictions
The query command may evaluated up to 500 times within a single evaluation process. Encountering this limit is fairly unlikely, but if you do, consider combining and optimizing the existing queries to bring the number down. Be especially careful with queries in loops.
Optimization tips
Using sortCollection & filter
The commands sortCollection and filter can be useful in reducing the amount of queries you need in your logic. While it is possible to do sorting and filtering within the query itself, if you need multiple differently filtered and/or sorted views of the same kind of records, it is more efficient to do a single query for a larger set of records and then filter and/or sort it rather than doing several queries.
Compare this:
<query var="lowQuantity" select="SELECT Id, Name, ListPrice, Quantity FROM OpportunityLineItem WHERE OpportunityId='${mainOppId}' AND Quantity < 10"><query var="highQuantity" select="SELECT Id, Name, ListPrice, Quantity FROM OpportunityLineItem WHERE OpportunityId='${mainOppId}' AND Quantity > 99">To this:
<query var="lineItems" select="SELECT Id, Name, ListPrice, Quantity FROM ProductLineItem WHERE OpportunityId='${mainOppId}'"><filter var="lowQuantity" value="${lineItems}" itemName="lineItem" where="${lineItem.Quantity < 10}"><filter var="highQuantity" value="${lineItems}" itemName="lineItem" where="${lineItem.Quantity > 99}"> Both produce the same result, except that the latter version executes only one query and would allow further filtered views to be created.
Child records & toString
Often it is necessary to query data of several records as well as some of their child records. For example, Opportunity and OpportunityLineItem records. The most efficient way to do this is to use the IN operator in the child record query. The toString command can be helpful in doing this, as it can easily build the string of IDs needed for the query.
<query var="opportunities" select="SELECT Id, Name FROM Opportunity"><toString var="oppIDs" value="${opportunities}" itemName="opp" expr="'${opp.Id}'"><query var="lineItems" select="SELECT Product2.Name, Quantity, Opportunity.Id FROM OpportunityLineItem WHERE Opportunity.Id IN (${oppIDs})">As can be seen, this fetches the opportunities and their line items in just two queries. The line items' Opportunity.Id field can be used to associate each line item with its opportunity, if needed.
The only problem with this approach is when the numbers get too large - a query can return 2000 records at most, so when there are numerous parent records with numerous child records, the child record query may hit the limit. In that case it may be necessary to query the child records separately for each parent record.
<query var="opportunities" select="SELECT Id, Name FROM Opportunity"><forEach value="${opportunities}" var="opp"><query var="lineItems" select="SELECT Product2.Name, Quantity FROM OpportunityLineItem WHERE Opportunity.Id = ${opp.Id}">This way results in more queries, which then means more Salesforce API requests as well as slower performance. Needless to say, you should always try to use the former approach if possible.