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

Operator Advisor, Best practices to make Joined calls

SOLVED
Highlighted
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

Re: Operator Advisor, Best practices to make Joined calls

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

8 REPLIES 8

Re: Operator Advisor, Best practices to make Joined calls

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

Re: Operator Advisor, Best practices to make Joined calls

Hi Milan, 

 

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

 

Jasper

Re: Operator Advisor, Best practices to make Joined calls

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 

Re: Operator Advisor, Best practices to make Joined calls

Hi @fmok , 

 

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

 

Jasper

Re: Operator Advisor, Best practices to make Joined calls

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

                                }

                ]             

}

Re: Operator Advisor, Best practices to make Joined calls

@fmok

 

Looks great, thanks for the example! 

 

Kind regards, 

 

Jasper

 

Re: Operator Advisor, Best practices to make Joined calls

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

Re: Operator Advisor, Best practices to make Joined calls

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