IN clause Alternatives

Hey,

My name is Cláudia and I'm doing my thesis with OutSystems and it is related with expressiveness limitations in the Aggregates. In this context, I would like to ask you how do you usually solve the following use case:

- Imagine you have a table with products and that each product belongs to a category (as can be seen in the first picture below);

- Imagine you have a dropdown tags (as can be seen in the superior right corner in the same picture) to filter that table with products by one or more selected categories in that dropdown;

How would you filter that list of products by the selected categories given that the Aggregates don't currently support the IN clause and assuming that you can't use SQL? The desired result is the one that can be seen in the second picture. I know that using the Index function is probably the most common way of solving this problem despite the SQL one, but I am particularly interested in finding patterns of other possible solutions that also don't use SQL.

Thank you for your time!



Hi,

You could convert your aggregate to SQL and use the BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList action from sanitization module.

Please refer to this link for Building Dynamic SQL Statements the Right Way

Hope this helps!

Thank you

Hey Marco, thank you very much for replying :)

I am actually interested in solutions that don't use SQL. I forgot to mention this in the post, sorry. 

So can you tell me how you would solve this without using SQL?

Hi,

Apologies for misunderstanding. Though, If I have to stick with using aggregates, I would also use the Index function.

Thanks

It was my fault, don't worry :) thank you again for giving your feedback!

Without using SQL IN (which is definitly the cost effective way to go) another "solution" would be, that you

  • Cycle thru your selected categories
  • Use the FilterList Action on the aggreate result
  • Append the FilteredList to a ResultList
  • Display the ResultList


Ok, there is just one thing that I didn't understand.

You are saying that the ForEach that iterates the categories would have a cycle arrow to the ListFilter action, right? But the ListFilter action only has 1 outgoing arrow and it will return a list with only the products of the first category. If you connect the ListFilter action to the ForEach (in order to filter by more than one selected category) then on the second category iteraction the products list will only have the products of the first category, because you removed all the others in the condition ("Product.Category = SelectedCategories.Current.Value").

I'm adding a print to better understand if this is what you were saying:

actually more like this. you basically have to "fill up" the result list with every iteration. otherwise (just like in your case) you only get the last iterations result.


Very interesting solution Stefan, I hadn't found one until yours that didn't use two ForEach! You really helped me in my research with this, thank you!
I'm very excited to know if we can find even more possible patterns :D

Hi all,

I'm curious what is meant by using the Index function ? 

Dorine

Hey,
You can see in the following posts how the Index function can be used in an Aggregate filter to simulate the behaviour of the IN, just search for "index" to be easier to find in the second link:

https://www.outsystems.com/forums/discussion/53735/how-do-i-perform-in-operator-in-aggregate/

https://www.outsystems.com/ideas/2224/in-clause-in-aggregates?IsFromAdvancedSearch=True




Ok thank you for the links Claudia,

I had seen your post and had started on brewing my own solution.  It looks a lot like that index solution, but with a LIKE.

I guess, what really matters here to make a good choice, is some more details like how many possible filter values are there, on how many will a user on average filter, and most importantly, how many rows are in the table.

I think whatever solution we make up to fix this ( to be honest, incomprehensible! ) lack of capability of aggregates, it will always be slower than using sql with IN clause, and it will always be less 'low code' (read : easy and clean for citizen developers) than an unfiltered aggregate followed by a listfilter of some sort.


Just a remark on the listfilter solution proposed by Stefan, you will have to follow that up by some ListSort action, because you loose your sort order of the aggregate by filtering out one category at a time.

I had some fun today experimenting with these options, comparing how much time they take (on a small table! so doesn't say much about large tables), if anyone is interested, see attached oml.

Dorine

QDRAggregateNoInClause.oml

You are welcome :)

Yeah, you are right, but you also always want the more flexible possible solution in case later you need to add more possible values, for example. But at this point I was just interested in analyzing if this is a common use case/problem that OutSystems users have and I'm trying to quantify it.

I'm glad you mentioned the SQL IN clause because my thesis is related to it's implementation in the Aggregates so good news! ahahah So you would like to see the same functionality of the SQL IN in the Aggregates through it being a system action, is that what you are saying?

Well noticed, I'm going to take a look at your oml. Thank you for taking the time to analyse this problem with me :D

Your welcome Claudia.  

Yes, yes, yes, please conclude in your thesis that similar option as IN clause is much needed in Aggregates.  Preferably as simple as referring in the filter of the aggregate to any list available in the action. 

Dorine

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.