Multiple DataTables in return from query

[jensc]

Hello, this is my first post here so I hope I am posting in the correct location.

I'm having some issues with figuring out how to do joins and other filter functions on the Thingworx platform.

But I think I am getting the hang of it, at least a little bit.

I am having some issues with while doing an INNER join on two tables from the Operator Advisor addition to TWX.

in the output from my service I get both of the tables "mashed" together. I'll add an attachment to illustrate this.

I think I am getting the join and filter correct, but I am not sure. Here's that code:

var filter = {
"filters": {
"filters": [{
"dataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"fieldName": "ProcessingResourceUID",
"type": "EQ",
"value": ProcessingResourceUID
}],
"type": "AND"
},
"joins": [{
"type": "INNER",
"sourceDataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"sourceFieldName": "JobOrderUID",
"targetDataShapeName": "PTC.SCA.SCO.JobOrder",
"targetFieldName": "UID"
}]
};

var params = {
filter: filter,
dataShapeName: "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
};

var result = Things[me.GetDatabaseThingName()].Query(params);

I am trying to use the "ProcessingResourceUID" (which is my input) to get all JobOrders that are related to it through the "JobOrderProcessingResourceRequirement" table.

Output is set to infotable with datashape JobOrder.

Does this look correct to you? 

Thanks.

[PaiChung]

This looks like something provided by OA vs. the standard Query in Thingworx.

Since I do not know how that service works behind the scenes, could within reason query the raw from the two tables you are joining and join them within Thingworx?

Probably if you got this statement to work as is, likely much more efficient though.

[jensc]

Yes, this is from OA and it is on an external database. (so there's no "Data Table" entity for these tables).

I'd really want to use regular SQL queries but it doesn't seem like that's possible.

From my understanding "INNER Join" means that I get data for both tables, so it makes sense that it returns both in the output. But I've also tried both RIGHT and LEFT joins and they instead return nothing.

I guess I really need to figure out how this "Things[me.GetDatabaseThingName()].Query(params);" works.

I've tried reading up on these "functions" in the PTC help documentation. But maybe I've missed something.

Is this type of coding only used in Thingworx or is it some standard Javascript thing that I can research more?

[PaiChung]

Some of these functions are basic derivatives of what is available in java.

but the OA Query I'm not too sure how that works.

Like I said, you can pull in perhaps the raw from the two tables and join them once you have them in Thingworx.

There are infotable functions to help with that.

[jensc · Accepted solution]

Hello,

Tried finding some JSON filter information, but couldn't really find much.

But I did as you said. I queried everything from both tables into seperate infotables and then joined the tables with the code snippet called intersect.

Thanks for your help and time anyways!