Hello , I want to do a recursive action that calls an sql ( input = entity Id , output= list of entities) and then the list of results should be iterated again and so on , until the sql output is empty. Do you have any idea how to implement this in Outsystems?
This is the action I want to make recursive:
Hi,
I can not understand your question correctly.
If you pass entity ID then only one record will come as output. (filter by ID column).
If you can add an example it would really help.
I added a print to the question
If you are using Advanced SQL, why dont you do it in SQL only. You can search for Hierarchical data query.
What DB (Oracle/SQL) you are using. let me try if I can get something for you.
True , I can do that in SQL but my query is already complex, I am using SQl
you can go through this link, detailed explanation on hierarchical data extraction with SQL CTE:
https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/
Instead of recursive calls, you should be able to use a stack (list) and call until the stack (list) is empty. https://stackoverflow.com/questions/159590/way-to-go-from-recursion-to-iteration
If you want to proceed with recursive solution out of your SQL node:
First of all, if you have a recursive call, you will call the action itself inside her and need to have a break condition (to avoid infinite loops and to retrive the result backwards).
The other point is that you will need to have the inputs for your action avalilable in any context inside your action to feed the recursivity.
You can check this article about that:
https://www.linkedin.com/pulse/recursividade-em-outsystems-ricardo-pereira/
(It's in portuguese, you will need to translate in browser).
Hope this can help.
Best regards,
Ricardo Pereira
Hello Beatriz,
I propose the same solution as Ricardo Pereira has already described, I would just like to elaborate a bit more and with a visual. The general idea is:
1. Create a server action called GetEntityDependencies.Let's say it takes an input parameter - EntityId
2. Create SQL query 'GetDependenciesByEntityId' that takes an Input - EntityId and Outputs - a List of Entities.
3. Loop through the SQL output GetDependencies.List and call server action GetEntityDependencies again. This is the recursion you seek. The server action will get passed the EntityId for each item in the List of Entities.
The server action will keep calling itself recursively until there is no Entity with 'sub entities'.
Hope the visual helped, but do let us know if you have questions,
AJ
AJ's solution works but you need to finish all the iterations under the server action time limit. I believe it is five minutes. If you Wrap your server action in a process you can spawn a new process passing any data as an input parameter, this allows to to immediately end the prior server action. This is a good solution unless you need to carry data of the type object to the next iteration because processes dont allow that type as an input parameter.