Skip to main content
J
jasperlg1-Visitor
1-Visitor
February 15, 2019
Solved

Operator Advisor, Best practices to make Joined calls

  • February 15, 2019
  • 2 replies
  • 4432 views

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? 

Best answer by mthakker

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

2 replies

M
mthakker5-Regular MemberAnswer
5-Regular Member
February 18, 2019

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

J
jasperlg1-VisitorAuthor
1-Visitor
February 18, 2019

Hi Milan, 

 

Thanks for the answer. I'm looking forward to this feature, we won't start writing our own joins then. 

 

Jasper

F
fmok12-Amethyst
12-Amethyst
February 18, 2019

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.

 

screenshot-1.png 

J
jasperlg1-VisitorAuthor
1-Visitor
February 20, 2019

Hi @fmok , 

 

It looks like something went wrong, I can't see your screenshot. 

 

Jasper

F
fmok12-Amethyst
12-Amethyst
February 20, 2019

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

                                }

                ]             

}

    Terms & ConditionsAccessibility statement