Relational join query in ThingWorx

[ashishtamhane]

Is there something equivalent to a simple SQL Join query as below in ThingWorx:

SELECT T1.Name, T2.Name, T2.Date FROM

Table1 T1 INNER JOIN Table2 ON T1.Name = T2.Nname

[VladimirRosu]

Are you asking about this capability for an infotable? Or are you asking how to execute SQL commands to an external SQL table from within ThingWorx?

[ashishtamhane]

Capability for an infotable is desired which does all the querying in a single statement as opposed to:

1. SELECT T1.Name FROM Table1

2. SELECT T2.Name FROM Table2

3. Perform join query on above 2 tables

ie we would like to reduce the Select statement cost as it will be expensive in Production.

[VladimirRosu]

Intersect is the snippet to use: https://www.ptc.com/en/support/article/CS340098?source=search

But...one very important point: the amount of data that you will retrieve in the beginning. I understand that SELECT could be expensive, but if you're querying (initially) millions of rows that then you keep as infotables, then you'll have an impact on memory...

[ashishtamhane]

Thats what my query is. Do we have something that selects and joins the data in the same query so as to reduce the amount of rows returned.

[olivierlp]

Hi @ashishtamhane ,
I wanted to see if you got the help you needed.
If so, please mark the appropriate reply as the Accepted Solution. It will help other members who may have the same question.
Please note that industry experts also review the replies and may eventually accept one of them as solution on your behalf.
Of course, if you have more to share on your issue, please pursue the conversation. 

Thanks,

[mstarnaud]

Hi Ashish

I'm not an expert on this, but I can share an example that my team has built.

There are 2 tables in our example : Attributes, and AttributeValues. They each have their own DataShape. The AttributeValue table includes column AttributeUid, which joins on Attributes' Uid. So here is what they did for a Select : 

let MyFilter = {
select: [{dataShapeName: 'CompanyNameHere.AttributeValue', fieldName: 'Value'},
{dataShapeName: 'CompanyNameHere.AttributeValue',fieldName: 'AttributeUid'}],
filters: {
filters: [{dataShapeName: 'CompanyNameHere.AttributeValue',fieldName: 'IsProcessed',type: 'EQ',value: 0}]
},
joins: [{type: 'INNER', sourceDataShapeName: 'CompanyNameHere.AttributeValue', sourceFieldName: 'AttributeUid', targetDataShapeName: 'CompanyNameHere.Attribute', targetFieldName: 'Uid'}]
};

result = me.GetManyOf({Filter: MyFilter, DataShapeName: 'CompanyNameHere.AttributeValue'});

I believe this is the equivalent of this query : 

SELECT av.Value, av.AttributeUid
FROM AttributeValues av 
JOIN Attributes a ON a.Uid = av.AttributeUid
WHERE av.IsProcessed = 0

Let us know if that helps.