SmartStore provides a set of helper methods that build query strings for you.
For retrieving data from a soup, SmartStore provides helper functions that build query specs for you. A query spec is similar to an index spec, but contains more information about the type of query and its parameters. Query builder methods produce specs that let you query:
Everything (”all” query)
Using a Smart SQL
For exact matches of a key (”exact” query)
For full-text search on given paths (”match” query)
For a range of values (”range” query)
For wild-card matches (”like” query)
To query for a set of records, call the query spec factory method that suits your specifications. You can optionally define the index field, sort order, and other metadata to be used for filtering, as described in the following table:
Describes what you’re searching for; for example, a name, account number, or date.
beginKey
(Optional in JavaScript) Used to define the start of a range query.
endKey
(Optional in JavaScript) Used to define the end of a range query.
matchKey
(Optional in JavaScript) Used to specify the search string in an exact or match query.
orderPath
(Optional in JavaScript—defaults to the value of the path parameter) For exact, range, and like queries, specifies the indexed path field to be used for sorting the result set. To query without sorting, set this parameter to a null value. Note: Mobile SDK versions 3.2 and earlier sort all queries on the indexed path field specified in the query.
order
(Optional in JavaScript)
JavaScript: Either ascending (default) or descending.
iOS: Either kSFSoupQuerySortOrderAscending or kSFSoupQuerySortOrderDescending.
Android: Either Order.ascending or Order.descending.
pageSize
(Optional in JavaScript. If not present, the native plug-in calculates an optimal value for the resulting Cursor.pageSize) Number of records to return in each page of results.
For example, consider the following buildRangeQuerySpec() JavaScript call:
In JavaScript build* functions, you can omit optional parameters only at the end of the function call. You can’t skip one or more parameters and then specify the next without providing a dummy or null value for each option you skip. For example, you can use these calls:
As a base rule, set pageSize to the number of entries you want displayed on the screen. For a smooth scrolling display, you can to increase the value to two or three times the number of entries shown.
Note
JavaScript:
buildAllQuerySpec(indexPath, order, pageSize, selectPaths) returns all entries in the soup, with no particular order. order and pageSize are optional, and default to “ascending” and 10, respectively. The selectPaths argument is also optional.
1public static QuerySpec buildAllQuerySpec(2 String soupName,3 String path,4 Order order,5 int pageSize)67public static QuerySpec buildAllQuerySpec(8 String soupName,9 String[] selectPaths,10 String orderPath,11 Order order,12 int pageSize);
Query with a Smart SQL SELECT Statement
Executes the query specified by the given Smart SQL statement.This function allows greater flexibility than other query factory functions because you provide your own SELECT statement. See Smart SQL Queries.
The following sample code shows a Smart SQL query that calls the SQL COUNT function.
JavaScript
1var querySpec = navigator.smartstore.buildSmartQuerySpec("select count(*) from {employees}", 1);23navigator.smartstore.runSmartQuery(querySpec, function(cursor){4 // result should be [[ n ]] if there are n employees5});
In JavaScript, pageSize is optional and defaults to 10.
iOS native
In Mobile SDK 8.0 and later, the native Swift SmartStore extension provides two ways to run a Smart SQL query.
1SFQuerySpec* querySpec =2 [SFQuerySpec3 newSmartQuerySpec:@"select count(*) from {employees}"4 withPageSize:1];5NSArray* result = [_store queryWithQuerySpec:querySpec pageIndex:0 error:nil];6// result should be [[ n ]] if there are n employees
Android native:
1try{2 JSONArray result =3 store.query(QuerySpec.buildSmartQuerySpec(4 "select count(*) from {Accounts}", 1), 0);5 // result should be [[ n ]] if there are n employees6 Log.println(Log.INFO, "REST Success!", "\nFound " +7 result.getString(0) + " accounts.");8}catch(JSONException e){9 Log.e(TAG, "Error occurred while counting the number of account records. "10 + "Please verify validity of JSON data set.");11}
Query by Exact
Finds entries that exactly match the given matchKey for the indexPath value. You use this method to find child entities of a given ID. For example, you can find opportunities by Status.
JavaScript:
In JavaScript, you can set the order parameter to either “ascending” or “descending”. order, pageSize, and orderPath are optional, and default to “ascending”, 10, and the path argument, respectively. The selectPaths argument is also optional.
The following JavaScript code retrieves children by ID:
1var querySpec = navigator.smartstore.buildExactQuerySpec(2 “sfdcId”,3 “some-sfdc-id”);4navigator.smartstore.querySoup(“Catalogs”,5 querySpec, function(cursor){6 // we expect the catalog to be in:7 // cursor.currentPageOrderedEntries[0]8});
The following JavaScript code retrieves children by parent ID:
In iOS, you can set the order parameter to either kSFSoupQuerySortOrderAscending or kSFSoupQuerySortOrderDescending. To narrow the query’s scope to certain fields, use the second form and pass an array of field names through the withSelectPaths parameter.
In Android, you can set the order parameter to either Order.ascending or Order.descending. To narrow the query’s scope to certain fields, use the second form and pass an array of field names through the selectPaths parameter.
1public static QuerySpec buildExactQuerySpec(2 String soupName, String path, String exactMatchKey,3 String orderPath, Order order, int pageSize)45public static QuerySpec buildExactQuerySpec(6 String soupName, String[] selectPaths, String path,7 String exactMatchKey, String orderPath,8 Order order, int pageSize);
Query by Match
Finds entries that exactly match the full-text search query in matchKey for the indexPath value. See Using Full-Text Search Queries.
JavaScript
In JavaScript, you can set the order parameter to either “ascending” or “descending”. order, pageSize, and orderPath are optional, and default to “ascending”, 10, and the path argument, respectively. The selectPaths argument is also optional.
In iOS, you can set the order parameter to either kSFSoupQuerySortOrderAscending or kSFSoupQuerySortOrderDescending. To narrow the query’s scope to certain fields, use the second form and pass an array of field names through the withSelectPaths parameter.
In Android, you can set the order parameter to either Order.ascending or Order.descending. To narrow the query’s scope to certain fields, use the second form and pass an array of field names through the selectPaths parameter.
1public static QuerySpec buildMatchQuerySpec(2 String soupName, String path, String exactMatchKey,3 String orderPath, Order order, int pageSize)45public static QuerySpec buildMatchQuerySpec(6 String soupName, String[] selectPaths, String path,7 String matchKey, String orderPath, Order order,8 int pageSize)
Query by Range
Finds entries whose indexPath values fall into the range defined by beginKey and endKey. Use this function to search by numeric ranges, such as a range of dates stored as integers.
By passing null values to beginKey and endKey, you can perform open-ended searches:
To find all records where the field at indexPath is greater than or equal to beginKey, pass a null value to endKey.
To find all records where the field at indexPath is less than or equal to endKey, pass a null value to beginKey.
To query everything, pass a null value to both beginKey and endKey.
JavaScript
In JavaScript, you can set the order parameter to either “ascending” or “descending”. order, pageSize, and orderPath are optional, and default to “ascending”, 10, and the path argument, respectively. The selectPaths argument is also optional.
In iOS, you can set the order parameter to either kSFSoupQuerySortOrderAscending or kSFSoupQuerySortOrderDescending. To narrow the query’s scope to certain fields, use the second form and pass an array of field names through the withSelectPaths parameter.
In Android, you can set the order parameter to either Order.ascending or Order.descending. To narrow the query’s scope to certain fields, use the second form and pass an array of field names through the selectPaths parameter.
1public static QuerySpec buildRangeQuerySpec(2 String soupName, String path, String beginKey,3 String endKey, String orderPath, Order order, int pageSize)45public static QuerySpec buildRangeQuerySpec(6 String soupName, String[] selectPaths, String path,7 String beginKey, String endKey, String orderPath,8 Order order, int pageSize);
Query by Like
Finds entries whose indexPath values are like the given likeKey. You can use the “%” wild card to search for partial matches as shown in these syntax examples:
To search for terms that begin with your keyword: “foo%”
To search for terms that end with your keyword: “%foo”
To search for your keyword anywhere in the indexPath value: “%foo%”
. Use this function for general searching and partial name matches. Use the query by “match” method for full-text queries and fast queries over large data sets.
Query by “like” is the slowest query method.
Note
JavaScript:
In JavaScript, you can set the order parameter to either “ascending” or “descending”. order, pageSize, and orderPath are optional, and default to “ascending”, 10, and the path argument, respectively. The selectPaths argument is also optional.
In iOS, you can set the order parameter to either kSFSoupQuerySortOrderAscending or kSFSoupQuerySortOrderDescending. To narrow the query’s scope to certain fields, use the second form and pass an array of field names through the withSelectPaths parameter.
In Android, you can set the order parameter to either Order.ascending or Order.descending. To narrow the query’s scope to certain fields, use the second form and pass an array of field names through the selectPaths parameter.
1public static QuerySpec buildLikeQuerySpec(2 String soupName, String path, String likeKey,3 String orderPath, Order order, int pageSize)45public static QuerySpec buildLikeQuerySpec(6 String soupName, String[] selectPaths,7 String path, String likeKey, String orderPath,8 Order order, int pageSize)
Executing the Query
In JavaScript, queries run asynchronously and return a cursor to your success callback function, or an error to your error callback function. The success callback takes the form function(cursor). You use the querySpec parameter to pass your query specification to the querySoup method.
In Smart SQL query specs, you can limit the list of fields that the query returns by specifying the fields in the Smart SQL statement. For other types of query specs, you can do the same thing with the selectPaths parameter. When this argument is used, the method returns an array of arrays that contains an array for each element that satisfies the query. Each element array includes only the fields specified in selectPaths. This parameter is available for “all”, “exact”, “match”, “range”, and “like” query specs.
Here’s an example. Consider a soup that contains elements such as the following:
Let’s run a “like” query that uses “ab%” as the LIKE key and name as the path. This query returns an array of objects, each of which contains an entire element:
Now let’s refine the query by adding _soupEntryId and name as selected paths. The query now returns a more efficient array of arrays with only the _soupEntryId and name field values:
1[[1, "abc"], [2, "abd"], ...]
Retrieving Individual Soup Entries by Primary Key
All soup entries are automatically given a unique internal ID (the primary key in the internal table that holds all entries in the soup). That ID field is made available as the _soupEntryId field in the soup entry.
To look up soup entries by _soupEntryId in JavaScript, use the retrieveSoupEntries function. This function provides the fastest way to retrieve a soup entry, but it’s usable only when you know the _soupEntryId:
j