topic Re: Relational join query in ThingWorx in ThingWorx Developers

Relational join query in ThingWorx

ashishtamhane — Fri, 07 Jun 2024 10:29:16 GMT

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

Re: Relational join query in ThingWorx

VladimirRosu — Fri, 07 Jun 2024 11:18:09 GMT

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?

Re: Relational join query in ThingWorx

ashishtamhane — Fri, 07 Jun 2024 11:59:01 GMT

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.

Re: Relational join query in ThingWorx

VladimirRosu — Fri, 07 Jun 2024 12:32:44 GMT

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...

Re: Relational join query in ThingWorx

ashishtamhane — Fri, 07 Jun 2024 14:49:41 GMT

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.

Re: Relational join query in ThingWorx

mstarnaud — Fri, 07 Jun 2024 15:01:46 GMT

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.

Re: Relational join query in ThingWorx

olivierlp — Mon, 17 Jun 2024 20:53:46 GMT

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,