Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X
Hi all,
We are implementing Operator Advisor (in TW 8.4). It's clear that every table in the database has a copy in ThingWorx as a DataShape, also junction tables.
I'm wondering, what is the best way to show data from multiple tables in the database? The way I see it there are two options:
1. Use the Operator Advisor database services and make multiple calls and do intersects on the infotables you'll get as an answer.
2. Write your own JOIN query's in the database thing and display the answer in a datatable.
Another question leaning on the previous question: What datashape do you use when you display the joined data? Or do you make a new DataShape for every new call?
Solved! Go to Solution.
Hi Jasper,
For the time being I would not recommend approach 1: ThingWorx only gets the last 500 records, so your intersects will only occur on the last 500 records. That being said, I would also not recommend approach 2 as anything other than a temporary measure - we will be releasing the ability to make basic SQL joins out of the box in the next Operator Advisor release. @fmok can provide more details about exactly what we plan.
As for what datashape to use - use the datashape that you ultimately wish to return - for example, if you're looking for "Work Definitions" to come back after being joined and filtered, use that, and it will bring back additional properties that you have added as well.
Thanks,
Milan
Hi Jasper,
For the time being I would not recommend approach 1: ThingWorx only gets the last 500 records, so your intersects will only occur on the last 500 records. That being said, I would also not recommend approach 2 as anything other than a temporary measure - we will be releasing the ability to make basic SQL joins out of the box in the next Operator Advisor release. @fmok can provide more details about exactly what we plan.
As for what datashape to use - use the datashape that you ultimately wish to return - for example, if you're looking for "Work Definitions" to come back after being joined and filtered, use that, and it will bring back additional properties that you have added as well.
Thanks,
Milan
Hi Milan,
Thanks for the answer. I'm looking forward to this feature, we won't start writing our own joins then.
Jasper
Hi Jasper,
We are targeting our March release to handle joins.
This will be done through the existing filters. Here is an example of what your filter may look like with the new join tags.
Hi Jasper
Here is the same info in text. Hope it formats ok...
{
"select": [
{
"dataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement_AP",
"fieldName": "Name"
},
{
"dataShapeName": "PTC.SCA.SCO.JobOrder_AP",
"fieldName": "Name"
},
{
"dataShapeName": "PTC.SCA.SCO.JobOrder",
"fieldName": "ID"
},
{
"dataShapeName": "PTC.SCA.SCO.ProcessingResource",
"fieldName": "Name"
}
],
"filters": {
"filters": [
{
"dataShapeName": "PTC.SCA.SCO.ProcessingResource",
"fieldName": "ID",
"type": "EQ",
"value": "WC1"
}
],
"type": "AND"
},
"joins": [
{
"type": "INNER",
"sourceDataShapeName": "PTC.SCA.SCO.JobOrder",
"sourceFieldName": "UID",
"targetDataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"targetFieldName": "JobOrderUID"
},
{
"type": "INNER",
"sourceDataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"sourceFieldName": "ProcessingResourceUID",
"targetDataShapeName": "PTC.SCA.SCO.ProcessingResource",
"targetFieldName": "UID"
}
],
"sorts":[
{
"dataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement_AP",
"fieldName": "Name",
"isAscending": true,
"isCaseSensitive": false
},
{
"dataShapeName": "PTC.SCA.SCO.JobOrder_AP",
"fieldName": "Name",
"isAscending": false,
"isCaseSensitive": false
}
]
}
@fmok for the record, does the join filter already exists in the current JSON syntax? Or is it the syntax that you will develop for OA?
Hi Jasper, The join filter is a new tag that we will add to the filter in OA