Literature Review: OutSystems vs IN Operator
Challenges and Solutions of Implementing the IN Operator in OutSystems
The OutSystems platform seems to have a bittersweet relationship with the IN operator. In this article I’ll discuss this relationship, i.e. the challenges of implementing the IN operator in OutSystems, and explore the available solutions to work around this gap, taking into account what can be found in the literature.
The IN Operator in OutSystems
The IN operator is an essential tool in database operations that allows filtering results based on a list of values. However, the absence of IN Operator in OutSystems Aggregate has been a consistent topic of discussion within the developer community. This absence can complicate common tasks, such as filtering records based on specific values.
Much has already been written by OutSystems itself or in the forums in order to provide solutions or workarounds with regard to the IN operator, in Aggregates and Advanced SQL.
For several years now, many OutSystems community members have been asking for the IN operator to be inserted into the OutSystems platform, but without success. For instance, in 2015 an idea was posted in the community, the IN clause in Aggregates, by Alexandre Scheurkogel. And over the years, many have posted the same idea, which was eventually merged with the original idea. It’s true that it’s something that’s already on the OutSystems radar, but it’s such a popular idea that more than 500 people have already supported/requested that it be implemented. It’s actually the most trending and liked idea in the Aggregates & Queries category.
I like some of the comments on the idea page, and here are the comments of some OutSystems MVPs:
This idea is so necessary that Cláudia Rosalino addresses it in her Master’s Thesis. “This dissertation aims to increase the level of expressiveness of Aggregates by proposing different solutions for implementing IN and NOT IN clauses without compromising the overall experience for any type of user.” Imagine, a thesis in which the aim is to find solutions to this problem 🤯.
But until this much-desired idea is implemented, there are a few ways to overcome the situation.
Here we’ll explore the 3 ways in which the community has worked around this problem, the first two solutions only use Aggregates and the third one uses the OutSystems SQL tool:
- Filter after fetching data (Stefan Weber’s solution)
- Using the Index function in Aggregate
- Use the SQL tool (see forge component)
From what can be found in the literature and on forums, the workarounds are as follows:
Workaround #1: Filter after fetching data (Stefan Weber’s solution)
Without using SQL IN, which is definitely the cost-effective way to go, one “solution” would be, that you:
- Cycle through your selected categories
- Use the ListFilter Action on the Aggregate result
- Append the Filtered List to a Result List
- Display the Result List
In other words, it means applying the filtering process after using the Aggregate for the retrieval of all the records.
The problem with this solution is that it requires a bunch of logic components, and it also requires the retrieval of all the data, and only then the filtering is applied, which results in an efficiency problem.
I must emphasize that, as Stefan Weber points out, this is a “solution” that, although it works, should be used, only in an extreme case.
Workaround #2: Using the Index function in Aggregate
An alternative, in order to keep using the Aggregate instead of a SQL node, is using the Index function, to filter for Ids within a specific string keyword.
Based on this screenshot from Raymark Cosme, the first Aggregate returns all the Ids, to be used in ListAppendAll to map the field that is needed. On the String_Join you map again the field that is needed and use “#” as a separator.
On the second Aggregate, use the following filter:
Index("#"+ String_Join2.Text + "#", "#" + Technician.TerritoryId + "#") <> -1
The Index function can be used inside the filters of an Aggregate and, together with a Text variable that the user can build as a concatenation of Text values that he wants to use to filter. It can achieve a similar behavior to the one from the IN SQL operator.
This performs the IN operator. The return or test values should look like “#21#22#23#” and so on.
However, this solution can cause a table scan and poor performance on large tables.
There are many voices completely against this approach, and those who say they are against Workaround #2 are certainly making harsher comments about Workaround #1.
But there you go, these are workarounds, and they should be taken into account, understanding exactly what the advantages and disadvantages are.
In fact, whenever possible, and especially if the table is large, and you need this functionality, convert to an Advanced SQL, use the IN operator, and index the column.
As Remco Dekkinga states in “OutSystems 101: Aggregates vs SQL Queries,” Aggregates are used when creating a SELECT query to retrieve a list of data, without the need for SQL operators like ‘IN,’ ‘EXISTS,’ ‘ANY,’ or ‘ALL’. On the other hand, Advanced SQL is preferred when dealing with bulk operations or when one of the SQL operators mentioned previously is needed.
Workaround #3: Use the SQL tool
Now, the solution recommended by OutSystems’ own documentation is to use the SQL tool. You can find more information in this document.
The Dynamically building a “WHERE … IN (…)” clause document specifies that “to properly build the values for the “IN” clause, you should always use one of BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList functions available in the Sanitization extension.”
In other words, do sanitization to prevent code injection in SQL snippets containing untrusted content, such as data collected from end users.
In such cases, don’t forget to enable the Expand Inline property for the query parameter. This is required so that the input variable is evaluated as SQL and not just as a string.
By doing all this, you’ll avoid seeing the SQL Injection Warning in Service Studio. This warning means that your application can be vulnerable to SQL injection if you don’t take the necessary precautions.
It’s crucial to emphasize that using a ‘for each’ loop to construct the string intended for the IN operator is NOT advisable. While it may seem similar on the surface, these two approaches are fundamentally different.
Potential Solutions to Address the Problem
In the community, there are some suggestions for possible solutions to this problem.
There is a solution proposed by José Gonçalves. He suggests that “if there was an area where you need only to choose an attribute and associate a variable (text type) would make things quicker”
I have to say that by searching the community forum, I’ve often found them being suggested by other members of the OutSystems community, especially the 1st solution proposed by her, as shown in the figure below.
In other words, this is a problem that the community is seeking to resolve, both in terms of workaround and possible developments.
Why the Problem Persists: The Official Explanation
According to Fernando Moitinho, Principal Product Manager at OutSystems, the reasons are:
Implementing the IN operator is a matter of priority, and OutSystems’ engineering teams have been preoccupied with other initiatives (like ODC) that demand resources.
Adding the IN to an Aggregate requires some serious experience design as it is not as simple as adding a simple filter with a list of hardcoded values.
The challenge of implementing the IN operator within OutSystems has been a recurring topic of discussion and interest in the OutSystems community.
The absence of this feature has led to various workarounds, each with its advantages and drawbacks. Workarounds such as filtering after data retrieval and using the Index function have been proposed, but they come with their own set of limitations, such as reduced efficiency and complex code.
While the community has put forward alternative approaches and suggestions, the problem remains unresolved officially. OutSystems users have voiced their need for this feature, as evidenced by the popularity of related ideas and discussions. The delay in implementing the IN operator in Aggregate is attributed to the complexity and priority of other initiatives within OutSystems.
Share your thoughts on these challenges and potential solutions. What do you think about the current workarounds, and how do you envision the development of this feature in OutSystems?