QueryDef returns empty results when conditions are met | Community
Skip to main content
D
dwnuk
Level 3
February 20, 2023
Solved

QueryDef returns empty results when conditions are met

  • February 20, 2023
  • 1 reply
  • 1521 views

Hi,

I have the workflow that insert a personalized string for each recipient, based on the provided value. Personalized strings and values are columns from the table xxx:userStrings. I wrote the function getRandomID(value) which looks for all records from userStrings table with provided value, adds their IDs to an array and then randomly returns one of IDs. After IDs is returned, the flag @used is set to be true.

 

function getRandomId(val) { var q = xtk.queryDef.create( <queryDef schema="xxx:userStrings" operation="select"> <select> <node expr="@id"/> </select> <where> <condition boolOperator="AND" expr={"@stringValue = '" + val + "'"}/> <condition boolOperator="AND" expr={"@used = 0"}/> </where> </queryDef> ); var results = q.ExecuteQuery(); logInfo("-------------Query results: " + results); for each (var a in results) { outputArr.push(a.@id); } randomId = outputArr[Math.floor(Math.random()*outputArr.length)]; return randomId; }

For the smaller number of records (< 1000) it works fine but if it needs to run for larger batches, after some time it starts to return variables results (from above block of code) as empty. Clearly, it is not a true because I see and can query (using Query activity) records that fullfill above conditions.

 

Do you have any idea how to make it running?

 

Thanks,

Dominik

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 akshaaga

Hi @dwnuk,

You can use the top attribute of the select element to limit the number of records returned by the query. For example, you can set top="100" to limit the result set to the top 100 records that match the query conditions.

You can also add a condition that limits the query to only check for records that have not been used in a certain amount of time, such as the last 24 hours.

Another reason, when working with many records, can be that the outputArr array may grow too large and consume too much memory, leading to a memory overflow.

You can modify your function to use a cursor the below way:

function getRandomId(val) {
var randomId;
var q = xtk.queryDef.create(
<queryDef schema="xxx:userStrings" operation="select">
<select>
<node expr="@id"/>
</select>
<where>
<condition boolOperator="AND" expr={"@stringValue = '" + val + "'"}/>
<condition boolOperator="AND" expr={"@used = 0"}/>
</where>
</queryDef>
);
var cursor = q.ExecuteCursor();
var hasResults = cursor.Next();
var outputArr = [];
while (hasResults) {
var result = cursor.Current;
outputArr.push(result.@id);
hasResults = cursor.Next();
}
if (outputArr.length > 0) {
randomId = outputArr[Math.floor(Math.random()*outputArr.length)];
var updateQuery = xtk.queryDef.create(
<queryDef schema="xxx:userStrings" operation="update">
<where>
<condition expr={"@id = " + randomId}/>
</where>
<update>
<node expr="@used">1</node>
</update>
</queryDef>
);
updateQuery.ExecuteQuery();
}
return randomId;
}

You can then check if the array is non-empty, and if so, randomly select an ID from the array and update the corresponding record in the table to set the flag to 1.

1 reply

akshaaga
Adobe Employee
akshaagaAdobe EmployeeAccepted solution
Adobe Employee
February 20, 2023

Hi @dwnuk,

You can use the top attribute of the select element to limit the number of records returned by the query. For example, you can set top="100" to limit the result set to the top 100 records that match the query conditions.

You can also add a condition that limits the query to only check for records that have not been used in a certain amount of time, such as the last 24 hours.

Another reason, when working with many records, can be that the outputArr array may grow too large and consume too much memory, leading to a memory overflow.

You can modify your function to use a cursor the below way:

function getRandomId(val) {
var randomId;
var q = xtk.queryDef.create(
<queryDef schema="xxx:userStrings" operation="select">
<select>
<node expr="@id"/>
</select>
<where>
<condition boolOperator="AND" expr={"@stringValue = '" + val + "'"}/>
<condition boolOperator="AND" expr={"@used = 0"}/>
</where>
</queryDef>
);
var cursor = q.ExecuteCursor();
var hasResults = cursor.Next();
var outputArr = [];
while (hasResults) {
var result = cursor.Current;
outputArr.push(result.@id);
hasResults = cursor.Next();
}
if (outputArr.length > 0) {
randomId = outputArr[Math.floor(Math.random()*outputArr.length)];
var updateQuery = xtk.queryDef.create(
<queryDef schema="xxx:userStrings" operation="update">
<where>
<condition expr={"@id = " + randomId}/>
</where>
<update>
<node expr="@used">1</node>
</update>
</queryDef>
);
updateQuery.ExecuteQuery();
}
return randomId;
}

You can then check if the array is non-empty, and if so, randomly select an ID from the array and update the corresponding record in the table to set the flag to 1.

    D
    dwnukAuthor
    Level 3
    February 21, 2023

    Thanks, @akshaaga. So the behavior I have experienced is caused by memory overflow?

    I will check if using top attribute or the approach with using cursor. 

    akshaaga
    Adobe Employee
    akshaagaAdobe Employee
    Adobe Employee
    February 21, 2023

    Hi @dwnuk ,

    It's possible that the behavior you're seeing is caused by memory overflow, particularly if the function is processing a large number of records and storing all the IDs in memory at once.

    Terms & ConditionsAccessibility statement

    Loading footer...