search for tect in any order

search for tect in any order

  

I'm trying to see if there is a way to filter on a text in any order. 

I.E. I have done the normal thing and set up a filter that is: 

SyntaxEditor Code Snippet

attributeA like "%" + SearchText + "%"


So lets say attributeA has a really long sting like "38mm product b24 with some kind of ancillary material". If I were a user I could search for key words in this string as long as they were in order by inputting into the search box "38%b24%ancillary" and this would return a value, however if a user put this out of sequence I.E. "ancillary%38%b24" they would get no results. 


Is there anyway to make a filter that returns words in any order as long as the have a "%" between them? Or something similar. 

Hi,

You can split the the input string from the user and iterate the list of strings and for each one do atributeA like "%" + split string element +"%" and concatenate them all with an or.

Hope this helps,

Marcelo

Marcelo Ferreira wrote:

Hi,

You can split the the input string from the user and iterate the list of strings and for each one do atributeA like "%" + split string element +"%" and concatenate them all with an or.

Hope this helps,

Marcelo


Hi Marcelo,


Thanks for the reply.


What the best way to split a string in outsystems? 

Hi,

There is a extension called Text that have a action called String_Split

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi,

There is a extension called Text that have a action called String_Split

Regards,

Marcelo

Hi Marcello,


So the only way I can figure this to work is:

use string split, 

set up ...say.... 10x variables var1 -var10

loop through the structure and set each part of the string against a new variable. 

I.E. in string "wordA wordB wordC wordD" loops the assign var1=wordA, var2=wordB, var3= wordC, var4= wordD...


then set up my filter to read: attibuteA like (("%" + var1 + "%") or ("%" + var2 + "%") or ("%" + var3 + "%") or ("%" + var4 + "%") .....)


This seems a little convoluted to me, and I'm sure there's probably a simpler way. 


Is this what you had in mind? 


Hi,

Created an example for you.

Hope this helps.

Regards,

Marcelo

Hello Talis, if you are using SQL and your database is SQL Server, you can use "Contain", like in the example below:

CONTAINS(Column, ' First_term OR Second_term OR Third_Term OR etc ')

In this case, you can pre-process the search string, with the split, make a new string in the correct format and pass as input parameter to the SQL with the ESCAPE set to no (inline parameters) and use it inside the query like this:

WHERE
     CONTAINS(Column, @search)

Cheers,
Eduardo Jauch

Hi,

You can only use CONTAINS if the column is full-text indexed. And for that you need to do a change directly on the database. If anyone decides to use this approach check the link below to create the index.

https://www.outsystems.com/forums/discussion/8934/how-to-install-configure-and-use-full-text-search/#Create2

Best Regards,

Marcelo

Marcelo Ferreira wrote:

Hi,

You can only use CONTAINS if the column is full-text indexed. And for that you need to do a change directly on the database. If anyone decides to use this approach check the link below to create the index.

https://www.outsystems.com/forums/discussion/8934/how-to-install-configure-and-use-full-text-search/#Create2

Best Regards,

Marcelo

Thanks for pointing the link, Marcelo :)


Talis Krumins wrote:

then set up my filter to read: attibuteA like (("%" + var1 + "%") or ("%" + var2 + "%") or ("%" + var3 + "%") or ("%" + var4 + "%") .....)

Hi Talis, the above is not possible, as it's not a valid syntax for "like". In fact, I don't think it's possible to do what you want, unless you restrict the number of possible tokens to search for, and have that number of filter options (filtern = "" or attributeA like "%" + filtern + "%").

What you seem to want is a Google-like full text search, but that's typically not what relational databases are good at.

Solution

Hi Talis,

As was already mentioned in other posts above, if you use an aggregate you have to know the maximum number of tokens. Also mentioned was that you should split the search string and build the WHERE clause with that.

So, see if this works for you:

  • Use an SQL query instead of an aggregate
  • Use String_Split to have a list of tokens
  • Using that list of tokens, build the WHERE clause to use in the SQL query

See attached oml for a possible solution.

Cheers,

José

Solution

José Costa wrote:

Hi Talis,

As was already mentioned in other posts above, if you use an aggregate you have to know the maximum number of tokens. Also mentioned was that you should split the search string and build the WHERE clause with that.

So, see if this works for you:

  • Use an SQL query instead of an aggregate
  • Use String_Split to have a list of tokens
  • Using that list of tokens, build the WHERE clause to use in the SQL query

See attached oml for a possible solution.

Cheers,

José

Hi Everyone, thanks for the input. It's been very helpful so far.


So I've managed to implement a solution like Jose'. But I would like to take things one step further. 

Bearing in mind my SQL skills are minimal. I would now like to extend the solutions to search more attributes from the entity. 


So here's what I have.

Entity: Product with attributes Name, Brand Id (some other attibutes that are not important to this convo)

Entity: Brands with attributes; Id, Name, Company ID

Entity: Company with: Id, Name


What I want to do is adjust my search to search the attributes  {Brands}[Name] and {Company}[Name].


In the context of Jose's solution I am successfully searching the {Product}[Name] fields at the moment. 


How to I adjust the solution to do this? 


You can do it like this

SearchClause + "{Product}.[Name] like '%" + EncodeSql(String_Split.List.Current.Text.Value) + "%' and {Brands}.[Name] like '%" + EncodeSql(String_Split.List.Current.Text.Value) + "%'"

Also were is a good place to learn a bit about SQL:

https://www.w3schools.com/sql/sql_intro.asp

thank you.