Problem with xtk.queryDef with foreign key condition

[alexanderh76698]

Hello everyone,

I'm currenty trying to write a little piece of code which deletes all delivery logs that are linked to a cancelled delivery (or in some situations deliviers in "stop requested" state). The code first loads all delivieres which are in the specified states.

After that, I want to load all delivery logs which are linked to those deliveries (OT: I'm currently doing this by using 2 different database queries, because I don't know how to select data from 2 linked tabled with the xtk.queryDef, maybe somebody can give me some advises with this problem as well).

The problem with this is, that I can't query the foreign key in the schema "nms:broadlogrcp". The schema documentation tells me, that there is a foreign key called iDeliveryId. but when I try to use it in the xtk.queryDef (@deliveryID ??), I get an error that this field is unknown.

This is my code:

var deliveryQuery = xtk.queryDef.create(

  <queryDef schema="nms:delivery" operation="select">  

    <select>    

      <node expr="@id"/>  

      <node expr="@internalName"/>

      <node expr="@label"/>        

    </select>

      <where>    

        <condition expr="@state=81"/>  

      </where>  

  </queryDef>)

var deliveryRes = deliveryQuery.ExecuteQuery()

var idArr = []

for each (var delivery in deliveryRes.delivery)

{

  idArr.push(delivery.@id)

}

var logsQuery = xtk.queryDef.create(

  <queryDef schema="nms:broadlogrcp" operation="select">  

    <select>    

      <node expr="@id"/>  

      <node expr="@DeliveryId"/>

    </select>

      <where>            

    <condition expr={"@DeliveryId IN ('" + idArr.join("', '") + "')"}/>

      </where>  

  </queryDef>)

var logRes = logsQuery.ExecuteQuery()

What is the best practice on this problem?

Thanks in advance for any advices

Best Regards

Alex

[Vipul_Raghav]

Hi Alex,

Maybe this query will help.

var query = xtk.queryDef.create(<queryDef operation="select" schema="nms:recipient" xtkschema="xtk:queryDef">
                                 <select>
                                      <node expr="@id"/>
                                  </select>
                                   <where>
                                     <condition expr="@id != 0"/>
                                     <condition expr="@delivery-id" setOperator="IN">
                                                <subQuery schema="nms:delivery">
                                                   <select>
                                                         <node expr="@id"/>
                                                   </select>
                                                   <where>    
                                                         <condition expr="@state=81"/>  
                                                   </where>  
                                                </subQuery>
                                     </condition>
                                   </where>
                                 </queryDef> );

There are certain limitations when using queryDef. It depends on the JS interpreter memory size and hence at times can result in workflow failing due to out of memory error.

Additionally, the default limit of fetching records is set to 10000. Ensure that you add linecount attribute to queryDef when doing so in case broadLogs are more than 10,000.

Hope this helps.

Regards,

Vipul

[alexanderh76698]

Hello Vipul,

thanks for your advice with the subquery.

Unfortunately, I still have a problem regarding the DeliveryID. It stills gives me an error that "expr="@delivery-id" (Line no. 7 in your example) is unknown. It looks like that there is no way to access this foreign key inside the broadLogs.

I found out about a "sqlExec" method. It's probably not the best way to use direkt sql statements instead of the API methods, but currently I can't find a better way.

By the way, is there a documentation about all the methods you can use in javascript like "sqlExec()"?

Best regards,

Alex

[Vipul_Raghav · Accepted solution]

Hi Alex,

All such methods are listed in a documentation called jsAPI.chm

All you need it to get in touch with Adobe support who will validate the request against your contract and then grant you access.

Also I've tested the code this time and it should work for you

var query = xtk.queryDef.create(
<queryDef operation="select" schema="nms:broadLogRcp" xtkschema="xtk:queryDef">  
   <select>  
        <node expr="@id"/>  
    </select>  
     <where>  
       <condition expr="@id != 0"/>  
       <condition expr="[@delivery-id]" setOperator="IN">  
                  <subQuery schema="nms:delivery">  
                     <select>  
                           <node expr="@id"/>  
                     </select>  
                     <where>      
                           <condition expr="@state=85"/>    
                     </where>    
                  </subQuery>  
       </condition>  
     </where>  
</queryDef> );  
var res = query.ExecuteQuery();
for each (var res1 in res)
{
  logInfo(res1.@id);
}

Regards,
Vipul

[alexanderh76698]

Thank you!