Problem with xtk.queryDef with foreign key condition | Community
Skip to main content
A
alexanderh76698
Level 2
March 9, 2017
Solved

Problem with xtk.queryDef with foreign key condition

  • March 9, 2017
  • 4 replies
  • 4387 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by vraghav

    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

    4 replies

    vraghav
    Adobe Employee
    vraghavAdobe Employee
    Adobe Employee
    March 9, 2017

    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

    A
    alexanderh76698Author
    Level 2
    March 10, 2017

    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

    vraghav
    Adobe Employee
    vraghavAdobe EmployeeAccepted solution
    Adobe Employee
    March 10, 2017

    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

      A
      alexanderh76698Author
      Level 2
      March 30, 2017

      Thank you!

      Terms & ConditionsAccessibility statement

      Loading footer...