Any news on this? It would be tremendously useful...

I'm really surprised how such a state of the art platform lacks this sort of basic things.

The challenge starts from the Entity here.

1. Entity one to many relation is not treated as the list here. We should compare the list with the value with list taken from the entity.

2. Compare operator: If we need to iterate a list to compare, it need iterator specific operator which ends up in time taking loops. It would be like combination of '=' and Index with little logic.

Would Hashing method helps in equating the values?

Almost 2 years later. Do we need more votes ?
Does anyone know if this idea is on the road map ?

+1

Vote +1 as well. Come on guys!

+1

+ 9999

 It would be great to make it as close as possible to standard sql

Well,

   I have a workaround. I know people can agree or disable pointing out some pros and cons, however, it is a practical solution.  Forgive me to use portuguese but the examples are simple to understand...  https://www.youtube.com/watch?v=m39zJ6S1mno

Best regards

Luciano Schiavo

Thanks Luciano,

I don’t speak Portuguese though perfectly understand your solution. ??

You are welcome Frank

If I understand that example correctly, I would be careful about it because it will cause a table scan and poor performance on large tables.

J.Ja

Hi James,

    you can create indexes on tables to compensate some performance problems during the search. Everything has pros and cons. Here I managed the problem regarding the payback to use advanced queries. I keep a lot of optimizations when I use aggregate.

Luck

Indexes won't help here because you are using a function in the filter, it's got to run that function on every row. :( That's why I'm saying this is a performance problem on a bigger table. I've created a LOT of performance problems over the years like this. ;)

J.Ja

As with every problem, there are multiple ways of skinning the cat. 

In clauses aren't exactly scary - and some of these workarounds would straight up fail code reviews in traditional environments...

workarounds are wasting of time when simple "IN" clause in the aggregate could save lots of time. "IN" is not even a "CASE", its almost like = or <>. Its like create a new arithmetic, where there are only + but no - / *. Its REDICULOUS

I agree with you J.Ja.

Like a gun, I can use it to defense or attack.  It is up to you to decide how to use a tool. The options are there.

+1

Is this still not implemented yet after so long?

Still waiting for this.

Absolutely needed!  I keep running into these use cases and for something so conceptually basic forcing us to use Advanced SQL is a pain in the A$$. 

We still waiting on this OS

Totally ridiculous that needing to use an IN clause forces you to completely change over to a different component; this is kindergarten functionality in the SQL world. Massive hole in the system that seriously needs to be repaired.

+1000

If you're going to support SQL backends OutSystems, support the backend!  

+1

Any news? This would save hours of re-writting things just because this functionality doesn't yet exist. It's been years! 

+1 This needs to happen. Would allow for more advanced aggregation...

Even after we use the best practice recommendation, it still throws warning for inline expand parameter. Nice if i can directly use that BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList functions in aggregate. 

Great Idea. Be aware that on some Oracle versions the number of values in the IN clause are limited.

+1 I honestly can't believe this is not in place yet.

+1

It's a bit of a puzzle why this seemingly basic and fundamental query function is not there in a platform that is great at simplifying much more complex functionality.

good idea!!!

This would be tremendously useful and would avoid having to resort to advance SQL in many simple queries.

This would be tremendously useful and would avoid having to resort to advance SQL in many simple queries.

This would very very useful, even to comply with the best practice of avoiding advanced queries as much as possible.

I do not know why this feature is still not available.

Aggregate should have IN operator.

Really hope to see this one soon. Allowing a list input into aggregates for an IN filter will be extremely powerful and greatly decrease the modification/maintenance load for developers.

Yes you can use local lists and iterate on the local list to append over and over, and index to remove duplicates, but this is INCREDIBLY slow by comparison.

This is the age of 'big data' and everyone needs reports on the data collected over the years of life of their databases. Please work to add this functionality ASAP.

We had a report that because of having to do multiple aggregates via a list then combining results a report took 100+ seconds to run.  We wrote our own SQL (using the SQL generated by the aggregate as a starting point) and then used the IN clause.  The report now takes 5 seconds to run.  Please implement this ability in normal aggregates!

This should defenitly a must! It took me almost two hours to transform a simple filter aggregate in an advanced SQL just to insert a simple IN clause...

I had to rethink all of the aggregate logic, Nulls instead of 0s, TOP for max records, paginations, left joins instead of with or without, really frustating all because of a simple IN, because the user needs to filter a static entity lookup.

Please make this function available as soon as possible it would be really helpfull!

This is a massive, stupid hole in their system that has been a thorn in everyone's side forever and causes a huge amount of rework when someone makes a new requirement for a complex filter, etc. Yet they continually refuse to address it... not sure why people are even trying anymore. Furthermore, even the implementation in advanced sql sucks - how hard would it be to be able to pass a list type and have it safely placed into bind variables instead of each developer being responsible for ensuring that there is no injection attempt being made? Just really, really annoying.

This will be very handy IN & NOT IN operator. Hope that this will be implemented soon.

Any news on this?

Yes, this is a long pending request.

I agree with Schin, more than 12 years and 407 likes !!!

Louds of Ideias adding to this, some workarounds.

I think this will save lots of developing time in complex aggregation.

it will really saves a lot of time if this is implemented, since using advancedSQL does not bring any advantage from Traditional coding,  in most of my Reports i will be losing a lot of time just to make it work my SQL, it is really hard to maintain since not all developers have same background or familiarity with SQL Lanaguage.

it is nice to have, but the index function is also the workaround for this

No, INDEX is *not* a "workaround" for this, it is absolutely HORRIFIC on performance. I keep finding performance problems in my teams' stuff, and it is often because they thought INDEX was a "workaround" for this. INDEX does a full table scan, while an IN clause... well... it uses the index if one is available, and most queries that we want IN for, we are talking about a FK relationship.

J.Ja

Agree with Justin James, it is absolutely not a workaround. Outsystems handles lists (both server-side and client-side) pretty okayish, but not being able to use such lists inside aggregates for filters and having to find weird workarounds for it is a huge downside right now. I honestly expected this to be implemented 3 years ago when we first suggested it.

So far the workarounds I have seen consisted of turning the list into a string and looking up the term in it, which.. you know, isn't optimal, especially because we lose all of the advantages of having it in list format. 

It is just hilarious that Outsystems continues to ignore this request for incredibly basic functionality that causes so many horrendous workarounds and custom solutions. It is quite clear from looking at this thread how concerned they are with their client's needs. This would take any decent programmer about an hour to implement in both aggregate and advanced sql. Provide a list of any type for an IN clause, denote what field of the list object to use if the list is not primitive, provide any special formatting needed (such as date formatting), create a list of bind vars matching the list size and execute the query against the database with the IN clause included. Easy, boom, done. These people are ridiculous.

I agree with you Charles, 3 years should be enough for at least one explanation of why it is so difficult to implement.

Also agree with Charles and Alberto. Ignoring customers is not very customer friendly. 

This feature will really boost a lot when implementing muli select inputs reports, i will be waiting for this feature 

Thanks, everyone, for the great feedback.

First of all, I'm sorry for the long overdue of this idea. For sure is something that we understand would spare a lot of time and expertise required from development teams, and without it, we are pushing developers to use SQL instead.

The only reason this has not been tackled yet is a matter of priorities. We have been busy with other initiatives that demand many of our engineering teams. On ODC, in particular we are heavily investing in ensuring we have strong foundations that can then support a continuous fast innovation pace.

Also, 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.
Unfortunately, I can't promise anything for the short term, but for sure, enhancing Aggregates' expressiveness is something that is still on our radar as a must-have to increase developers’ productivity and reduce the required skillset.

Thanks for the enlightenment.

Regards

Alberto,

As a MS certified SQL developer, and an experienced Oracle developer, I am struggling to understand why you say this would need a lot of development.  It honestly would not, as the functionality is built in to all variations of SQL.  You would just need to strip "inappropriate" text from the IN criteria arguments to prevent SQL injection etc and feed it through.

We aren't asking for nested subqueries here, just a basic IN criteria.

It looks to EVERYONE here like your marketing team are deciding development priorities, and that they are opting for shiny new feature they can sell to non-technical management, instead of the basic features your customer-developers actually need.

Nathan

So my original post about this was in 2015. I remember at the time it got a lot of upvotes (I believe with in a year it was one of the most popular suggestions on the ideas board), and I've always had fun watching the emails come in. 

I get that Outsystems has priorities - I think I've come to accept that this is unlikely to ever be one.

NathanHobbs 

I didn't say that !!

Regards

@Fernando Moitinho Thank you for your reply. I do however have a hard time believing that this is a simple "we just didn't get to it" priority thingy. I also do not think that the people that make these choices have any idea how common this use pattern is for us developers. 

As @NathanHobbs said, we are not asking for subqueries. I simply want to be able to a a list of Integers (or texts) that I can then use in a filter expression with the IN/NOT IN keywords. So the statement could then become something like this;

ListOfIntegers.List.Empty is True or Entity.Id IN ListOfIntegers.List

All "magic" that then needs to be done to prevent injection can be handled by the compiler just like a lot of other injection preventions are already done by the platform. This make the created applications safer, something that can be used in marketing as key feature. All most all building blocks are already available so it should be viable to implement this. 

Please discuss this feature again internally. It is really needed. It is not a skill issue but more a maintenance and speed issue. And this is really important to our organization. 

IN and WITH are almost must have statements for modern SQL queries. So, it would be amazing and very useful to implement them in OS. 

Regards,

Sirajeddine

I wonder what security implications this could involve for screen aggregates - could open up vulnerabilities for hacking.

@Nicholas Campbell Could you please explain a bit more ?

@Sirajeddine Bouasker I am not sure how OutSystems would go about implementing, but when you use an IN clause in AdvancedSQL you have to set the 'Expand Inline' option to Yes which allows you to inject SQL into your query. Doing this in AdvancedSQL is safe enough since you can sanitize your inputs before they get to the AdvancedSQL. However, with a screen aggregate, it could take these inputs straight from the client side and inject them into the SQL - very dangerous as a hacker could abuse this. However, I am sure OutSystems will make this not possible through the way they implement it, by making sure they do this sanitization for us etc - so it's just something to be aware of. SQL injection is a very common hacking method.

Hi @Nicholas Campbell no worries, OutSystems got you covered:

Abstract from:

https://success.outsystems.com/documentation/best_practices/development/building_dynamic_sql_statements_the_right_way/

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. 

Thanks @Daniël Kuhlmann, my point is that since a parameter for a ScreenAggregate comes from the client side and straight into the query (without us being able to use the functions you mentioned on the server side), a hacker could inject his own SQL query and bypass the functions you mentioned. But I would trust OutSystems to do something like this in the AdvancedAggregate IN function when/if they build it so that it would do it server-side for us...

@Daniël Kuhlmann Thanks for documentation.  
@Nicholas Campbell Thank you. I got your point. Still, I think OutSystems may do this on the server-side, by making it only available for aggregates inside server actions, and not in the client-side aggregates.

Kind regards,
Sirajeddine

I think it is a best practice anyway, not to pass screen parameters or parameters like GetUserId() directly into aggregates/SQL queries, even though the platform allows it. 

So wherever the SQL query will be used, it should be wrapped and the parameters defined on the client side should never be directly allowed.

I agree Ozan, but screen aggregates can't be wrapped which is why I say it would be quite a security risk to allow an IN there - but perhaps Sirajeddine is correct and OutSytems will not allow an IN function for screen aggregates for this reason, or somehow put sanitization in the AggregateFeature behind the scenes for this IN parameter.

I see your point. In that case I would stop using screen aggregates, but that really takes away the advantage of the whole react paradigm.

I am not sure how ScreenAggregates work in terms of the code behind them in OutSystems, but I would think it is possible for OutSystems to build those Sanitisation methods that Daniel mentioned into the actual code so that as a developer we would not need to worry about the security... but that's why I commented my concern - to make sure this is taken into account when/If this is implemented.

Perhaps maybe Outsystems can create input parameters that we can manipulate for the aggregates?

Screen aggregates are secure rest end points under the hood.

@Nicholas Campbell you have any proof that these are not secure? 

My experience is that only they get less secure because developers pass data client side, that is not needed and can be retrieved server side (like getuserid).

@Daniël Kuhlmann these concerns are in the context of the proposed idea of including IN parameters, not the current ScreenAggregates they way they are now.

@Nicholas Campbell if OS implements IN in aggregates, I'm sure it's totally different from the way they are implemented in SQL. Ideally, the input is just a List of the right type, and OS will do whatever is needed to secure the input.

@Kilian Hekhuis that sounds like a good idea to eliminate the risks.

@Nicholas Campbell offering an IN clause to a screen aggregate doesn't really have any different security implications than simply using a filter with a single match clause, other than you could query for more than one thing at once. The security considerations for the developer are the same though. Any type of data sanitization, row level security, etc., must be managed by the developer in both scenarios.

@Craig St.Jean then why does OutSystems require the 'Expand Inline' option to be set to True for an IN statement parameter but not for WHERE parameters in the AdvancedSQL tool? (Although of course you could set it to true for a WHERE clause if you wanted it to be dynamic - I am assuming that this will never be dynamic for a WHERE clause in an aggregate but would be required to be dynamic for an IN clause - but perhaps this assumption is wrong...?)
See the below forum discussion - the ExpandInline allows for dynamic SQL which allows for SQL injection - which seems to be required for an IN statement. So I assume the same will apply to ScreenAggregates...

https://www.outsystems.com/forums/discussion/82078/expand-inline-parameters

Some documentation can be found here https://success.outsystems.com/documentation/best_practices/performance_and_monitoring/avoid_expand_inline_parameters_for_dynamic_values/

Hi Nicholas,

What you are saying isn't quite true, you can use non-inline expanded parameters for an IN statement in an SQL just fine, it's just that it isn't very useful, as you'd have a fixed amount of parameters in the IN list:

WHERE {MyEntity}.[MyValue] IN (@param1, @param2)

Your assumption that "the same will apply to Screen Aggregates" isn't based on anything, and even quite contrary to evidence: except for dynamic sorting (sort of), there's nowhere in an aggregate you explicitly specify parts of an SQL, and there's therefore no reason to assume that OS would implement this idea by making it vulnerable to SQL injection.

That said, Idea comments aren't meant for these lengthy discussions, so I'd like to ask everyone to stop responding. Take it to the forums if you do feel the need to discuss it further.

@Kilian Hekhuis as a closing statement from me.

I see what you have said there about specifying individual parameters without using ExpandInline but as you said - this is not all that useful since it is very limited; so the ExpandInline is generally required - at least I have always needed to use it.

I am not saying/assuming that OutSystems would do this badly and allow the risks, it is just a comment/warning and I feel that it is valid to raise it. I think it also serves to raise awareness to bring up these concerns since people should be aware of these types of things. No matter how much OutSystems takes care of risks for developers, there will still be places developers need to be careful, and security should always be something we consider when developing.

Hi everyone! I know it's redundant to say this is on our radar but ... it's on our radar. Like others have said there are implied security risks but there is also the matter of the dev experience in setting up and maintaining the input list in the Aggregates UI, managing TrueChange and even changes to our Language Model to support this type of clauses (IN, EXISTS and their NOT counterparts). Moreover our data shows that these clauses have a marginal utilization (in AdvancedQueries).

Also, taking into account other awesome ideas and feedback you guys have provided that have more reach and provide greater value has led to a lower prioritization in comparison. I truly believe we will have this sooner rather than later but right now it's not on the top of our list.

The ideas section is a phenomenal source of information but at the end of the day we need to mash it up with other sources and back our decisions with data so don't think for a second that we do not read this or that we do not "hear" you, most of the times it's just a matter of prioritization.

Hi Hélio,

Though I understand the need to balance usefulness and use, this is one of the most liked ideas. So even if it's not the most used construction in SQLs, its usefullness is undisputed, and many people would like to have it. So let's go for "sooner" 😁.

100% Right Kilian

Today it is possible to do this using index(), but it would really be much better for aggregates to already have this by default, I voted for your idea.

@Lucas Soares The outcome in terms of the result set are the same, but the performance is NOT THE SAME. Index() is absolutely the worst possible way to do this, it destroys the performances, please stop telling people that index() is an acceptable approach. The only way to do this properly at this time is to use a SQL statement with IN.

I am getting very sick and tired of seeing code written with index() because people have posted to the forums that this is a way to replace IN, I'm the person who has to explain to the client why the performance is terrible and then get called in to fix it.

J.Ja

I believe you may find this article interesting
Literature Review: OutSystems vs IN Operator

... or not 🤔 

@Nivaldo Pereira GREAT article, perfectly summarizes my thoughts and the overall situation and options! (And not just saying this because you quote me twice :D !) At this time, writing a SQL statement is the only viable option, unless your data set is small. The other options are really bad habits to get into, especially since junior developers get a hold of a technique and don't have the experience to know when it's the right one... same reason I never tell anyone about EXEC in SQL, eval() in languages that support it, lambdas/closures in the languages that support them, etc. A senior developer knows when these tricks are OK and when they are bad, but once the juniors figure out how to do them, you see them EVERYWHERE and they become this horrible disease in your code destroying maintainability and performance and security...

J.Ja

@Justin James I've also seen the Index() method used a lot as a substitute. For my benefit and I assume many on this thread; can you do a small comparison of the performance effects of using Index() vs using the expand inline parameter approach when using IN()? I ask since OutSystems warns against the performance impact of using the expand inline paramater since it can't optimise the query execution plan.

So if both have performance warnings, can you go into a little bit of detail as to which is worse? I understand the generic performance issue of using Index() but what I don't know is how much of an impact OutSystems' query excecution optimisation or lack thereof may play a part in determining which is worse. I assume OutSystems can optimise the Index() method which would possibly overcome some of the performance issues you warned against?

See below the OutSystems docs warning against performance issues when using the expand inline property neccessary for the IN() method you mentioned (when the IN stamement is dynamic of course).

At a high level, not being able to optimize the execution plan isn't that huge of a hit, because a fast query is still a fast query.  Using index() to filter makes for a slow query no matter how you slice it when compared to IN.

@Nicholas Campbell the comments from @Greg Whitten are spot on. I don't really have it in me to generate a dataset with a few million rows of realistic data to give you a comparison (and then test it under real world conditions so it accounts for execution plan caching and so on), but I'm sure there are plenty of SQL related sites that can give you a rough idea of the differences. But as Greg says, a fast query is always fast and a slow query is always slow, and this is giving up the use of the system indexes and forcing a table scan every time.

And no, OutSystems has no possible way of optimizing Index(). Once a table scan is forced, all bets are off on performance. :( This is why data model design (and to a lesser extent, UI/feature design) is so absolutely critical to performance. Virtually every poorly-performing enterprise app I've seen, if the problem was caused by a programming decision (as opposed to, say, a slow network), the root cause was ultimately caused by the data model. Even when it's something like "we are looping over a list of things and running the same query for each item in that list", that is something that can be usually solved with a data model change. It's not uncommon for me to have CRUD wrappers that quietly maintains a separate set of Entities derived from the ones being obviously used, just for weird use cases where I need to do a query or code that cannot perform well with the data model that is designed for the day-to-day operations.

J.Ja

+1 on this Idea. Looking forward to have this real soon. Thank you!

Please, OutSystems. Every time there's a search screen with statuses or something with multi select I want to quit and become a farmer.

one more on this idea

Just adding a note to this - we found a forum post related to this and this seems to work pretty well. Lets say you are looking at Tickets and one of the ticket attributes is DepartmentId. Lets say you wanted to pull all tickets from departments 2, 5 and 10. What you would do is have a text var ListOfIDsSepByPoundSign that you would put in "2#5#10" and then add the following filter to your aggregate.

("#"+ listOfIdsSepByPoundSign + "#") like ("%#" + Ticket.DepartmentId + "#%")

@Jason Herrington the performance with that will be approximately as terrible as the Index() method.

There is no possible substitute for doing this the right way unless the data set is small and the performance issues won’t be noticed. The “benefit” of forcing Aggregates to do something that produces equivalent results without actually having an IN clause is lost on me  it’s confusing to read the code because it isn’t code anyone would ever write, and the performance is bad too.

J.Ja

@Justin James - absolutely no argument from me on that - only case I could see it being nice is if someone doesn't know how or doesn't want to write the sql - although the new button to convert aggregate to advanced sql seems to make that alot easier.

@Justin James yes performance would be appalling as LIKE criteria can't use indexes on SQL Server.

LIKE and INDEX will both trigger a tablscan.

(LIKE "Fred*" will use indexes, LIKE "*Fred" or LIKE "*Fred*" will not.)

OutSystems won't even allow IN with a fixed list of hard coded values: Status IN ("Open","Closed")

There's no excuse for not at least adding this latter option.

Any idea if this is close to being implemented? Very useful!

Indeed very useful, we really miss some new development improovement tools, and this is one of the most desired one.

I hope this is implement for both ODC and O11, now that both platforms will live side by side and we can choose the stack according to our needs.

+1
I am currently doing the workaround by index function, but the perf is poor

Agree, having IN and BETWEEN in aggregates would make filtering much simpler and clearer. It would save time and avoid extra logic or SQL workarounds. Given the long discussion here, it’s unclear why the status hasn’t changed yet, is this still being considered by OutSystems?