Rita Silva
Rank: #14732
19Views
7Comments
Solved
It's possible to use regex_search in an aggregate filter ?
Question
Aggregate
Beginner
Traditional Web
Application Type
Traditional Web

Hi, 


I have the following problem and I don't know if it is possible to do it the way I am doing it, or if it will have to be a sql query, because I have many filters applied.

 In this case I have an aggregate with filters, and I am previously using the Regex_search action to obtain the client's first and last name and create a new attribute with output from that regex_search. 

The result is for example if I have John Peter Doe, you will find the same result if you just search for John Doe.

 When using the regex_search output I can make a filter like: Customers.Name (local variable) = Regex_SearchFirstName.PatternResult + "" + Regex_Search2Surname.PatternResult ?? or there is a better solution and I am not getting there! 

thank you

0
0
on 17th Jan
Copy link to comment
João Marques
Rank: #55
Solution

Hi Rita,


In SQL you have the LIKE option to search for text.

It is very usual to find it with the special character % which is the equivalent in * in Regex. With a simple change in your search expression you can achieve this very simply.

If you have Customers.Name like "%John%Doe%", records like John Doe, John Silver Doe, Paul John Doe would be retrieved, which I think is what you want.

Given that, you just need to replace the whitespace between characters for a % on your search keyword and use it in a like.

Like in the example below, where my variable SearchKeyword_SQL will take the value of what the user searches in SearchKeyword and replace the whitespaces with % and adding a % in the beginning and in the end (to allow matches if the user searches by middle names):


And then on the aggregate, you just need to add the filter with LIKE, in your case Customers.Name LIKE SearchKeyword_SQL.

 

Hope it helps.


Regards,
João

See solution in context
2
0
on 17th Jan
Copy link to comment
Rita Silva
Rank: #14732

Hi @João Marques ,

ohh I hadn’t even thought about it, so it definitely makes my job easier and I don’t need to look for the first and last name. 

I will try to do that, thank you very much again :D

0
0
on 17th Jan
Copy link to comment
Rui Barradas
Rank: #86

Hello there Rita,

Hope you're doing well.

There is no need to pass your Aggregate into a SQL Query just because you have many filters applied. Usually, an SQL Query is used to give you the flexibility to do things that you can't do using an Aggregate (for example, the usage of subqueries). But OutSystems Best Practices recommend you to use Aggregates when applicable (because they are optimized and easier to maintain).


About your filter, I don't see why you can't have it that way. It is a viable filter for an Aggregate like any other, it all depends if the Aggregate returns the records that you pretend.


Kind regards,

Rui Barradas

2
0
on 17th Jan
Copy link to comment
Rita Silva
Rank: #14732

Hi Rui,

I was doubting if it was possible, but if you say so, I will continue with the aggregate and apply the regex_search output. 

Thank you for the answer.

Best regards

0
0
on 17th Jan
Copy link to comment
Rui Barradas
Rank: #86

Replying to Rita Silva's comment on 17 Jan 2021 18:09:23

Can you please clarify exactly what you pretend to achieve?

If you give us a use case or some examples it would be great :)


To be honest, I also thought about Like operator as João Marques stated below, but I wasn't sure if it was the best option for you scenario.


Let's say as an example that Regex_SearchFirstName.PatternResult returns "John" and Regex_Search2Surname.PatternResult returns "Doe".

Then your filter is going to do a strict comparison:  Customers.Name = "John Doe".

Using the Like operator you will also get records like "John Undoe" or "Johnathan Doe". Probably it is indeed what you want to achieve :) it depends if you want to search for those exact words or if you want to search by strings where those words appear.


If your question was only related with the possibility of using a Regex_Search output in an Aggregate's filter, then yes, it is possible.


Kind regards,

Rui Barradas

0
0
on 17th Jan
Copy link to comment
Rita Silva
Rank: #14732

Replying to Rui Barradas' comment on 17 Jan 2021 18:26:19


Ahh yes well seen, you are probably right. 

Basically I am filling out a form where I am looking for clients with the same name, regardless of middle names, if there is one, a popup will open with similar ones.

But I have to see this part better and see what is the best option.

Thanks for the warning. 

0
0
on 17th Jan
Copy link to comment
João Marques
Rank: #55
Solution

Hi Rita,


In SQL you have the LIKE option to search for text.

It is very usual to find it with the special character % which is the equivalent in * in Regex. With a simple change in your search expression you can achieve this very simply.

If you have Customers.Name like "%John%Doe%", records like John Doe, John Silver Doe, Paul John Doe would be retrieved, which I think is what you want.

Given that, you just need to replace the whitespace between characters for a % on your search keyword and use it in a like.

Like in the example below, where my variable SearchKeyword_SQL will take the value of what the user searches in SearchKeyword and replace the whitespaces with % and adding a % in the beginning and in the end (to allow matches if the user searches by middle names):


And then on the aggregate, you just need to add the filter with LIKE, in your case Customers.Name LIKE SearchKeyword_SQL.

 

Hope it helps.


Regards,
João

2
0
on 17th Jan
Copy link to comment
Rita Silva
Rank: #14732

Hi @João Marques ,

ohh I hadn’t even thought about it, so it definitely makes my job easier and I don’t need to look for the first and last name. 

I will try to do that, thank you very much again :D

0
0
on 17th Jan
Copy link to comment
Felipe Santos
Rank: #913

Nice

0
0
on 17th Jan
Copy link to comment