6

I'm trying to retrieve some data from the local database using SmartStore and join queries. Let's say I have the following query:

SELECT * FROM {PricebookEntry}, {Product}, {Pricebook} WHERE {Pricebook:Name} = 'MyProducts' AND {Product:Id} = {PricebookEntry:Product2Id} AND {PricebookEntry:Pricebook2Id} = {Pricebook:Id}

According to the documentation only smart queries can do joins, so here is how I execute this query (in the console):

var querySpec = navigator.smartstore.buildSmartQuerySpec("SELECT * FROM {PricebookEntry}, {Product}, {Pricebook} WHERE {Pricebook:Name} = 'MyProducts' AND {Product:Id} = {PricebookEntry:Product2Id} AND {PricebookEntry:Pricebook2Id} = {Pricebook:Id}", 1000);
navigator.smartstore.runSmartQuery(querySpec, function(result){console.log(result);}, function(error){console.log(error);});

All works well and the callbacks behave as they should compared to all my other scenarios. I get a result object with currentPageOrderedEntries array property which holds all the results.

Typically each element in the array is an array of properties for the result record holding some key information about the soup entry as well as the actual record itself at the 1st index in that array. In all other queries (without joins) the record itself is a JSON object and always at the 1st index.

However, when I perform a join, I can see that the records specified first in the query (in this case "PricebookEntry") are coming back as expected - JSON objects at the first index, but all other records from the other objects specified in the join ("Product" and "Pricebook") are coming back as JSON strings rather than objects, and are positioned at a random index in the array. Looking at the example below, the Products are in array[8], Pricebooks are in array[14].

enter image description here

My question(s) are:

  1. Is this an expected behavior?
  2. Is there any way to know before hand at which index the result from the join will come?
  3. A better example of how to retrieve related records (I'm looking for Account with Contacts for an example and then I can figure it out myself for more complex scenarios).
6

Short answer - it works as expected

If you select * a soup, you will get all the "internal" columns we create to support smartstore operations (more on this below). Columns that hold data from "indexed paths" use the type specified in the indexed spec (string, integer, floating etc). Since we don't support a type object, when you index non-leaf nodes, you will get them back as strings and you will need to json-parse them your app.

Long answer

For each soup we create a table in the database with a column for the soup element, a column for the primary key, a couple of columns for created and last modified dates and a column for each indexed path.

So if your soup has two indexed paths pathA, pathB, and you insert the soup elements {id:1, pathA:a1, pathB:b1}, {id:2, pathA:a2, pathB:b2}. Your table should look something like:

| _soupEntryId | _soup      | TABLE_1_0 | TABLE_1_1 | created | lastModified |
|--------------+------------+-----------+-----------+---------+--------------|
|            1 | {id:1,...} | a1        | b1        | date1   | date1        |
|            2 | {id:2,...} | a2        | b2        | date2   | date2        |
|--------------+------------+-----------+-----------+---------+--------------|

When you write a SmartSQl query: - we generate a SQL query by replacing {soupName} with the actual table name TABLE_x and {soupName:path} with the actual column name TABLE_x_y - when we process the result set, before giving it back to the application, we turn data coming from the _soup column into json object, and we make sure the data for TABLE_x_y fields are returned according to the index spec type.

Recommendation

Instead of select * you should select the columns you are really interested in getting. Make sure to json-parse any indexed paths that you know contain json (but that smartstore believes are string - based on your index spec type).

In the future

Maybe we should consider adding a new index spec type (we could call it json) for the cases where you want to index non-leaf nodes.

  • Thanks Wolfgang, appreciate the detailed answer! I select * just because I have only 3-4 fields per soup, but I get what you mean. Identifying the JSON and parsing it is easy, though the last part of the puzzle is the index in currentPageOrderedEntries. It's not always guaranteed to be the same, so is there any way to know at which index the JSON string will appear? – Boris Bachovski Nov 23 '15 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.