cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Stay updated on what is happening on the PTC Community by subscribing to PTC Community Announcements. X

Operator Advisor, Best practices to make Joined calls

jasperlg
12-Amethyst

Operator Advisor, Best practices to make Joined calls

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
mthakker
6-Contributor
(To:jasperlg)

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

View solution in original post

8 REPLIES 8
mthakker
6-Contributor
(To:jasperlg)

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

jasperlg
12-Amethyst
(To:mthakker)

Hi Milan, 

 

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

 

Jasper

fmok
5-Regular Member
(To:jasperlg)

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 

jasperlg
12-Amethyst
(To:fmok)

Hi @fmok , 

 

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

 

Jasper

fmok
5-Regular Member
(To:jasperlg)

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

                                }

                ]             

}

jasperlg
12-Amethyst
(To:fmok)

@fmok

 

Looks great, thanks for the example! 

 

Kind regards, 

 

Jasper

 

jasperlg
12-Amethyst
(To:fmok)

@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? 

fmok
5-Regular Member
(To:jasperlg)

Hi Jasper, The join filter is a new tag that we will add to the filter in OA

Top Tags