What is the best way to search my datasource with 4 different user inputs.
Application Type
Reactive

Hi,

I have 4 user inputs, where one or more may be contain text but some may be empty. I am trying to find a good approach to search my database based on these inputs. The inputs can be any column in the table. My database is fairly large so retrieving all results and then applying a sort is not an option.

The picture above is a mock. The user inputs "BAW" and the the app searches for "BAW" in all 4 columns of the database. The results returned are as shown. It must be possible to provide multiple inputs however, and I cannot figure out a good approach.

Does anyone have any suggestions for me?

Kind regards.

Hi Kristian,

if you have a fixed number of 4 inputs to search for it should be simple.

Implement in the aggregate/sql some filter like this for all the columns you need:

"where 

   (input1 = "" or db.callsign like '%input1%') OR

   (input2 = "" or db.callsign like '%input2%') OR

   (input3 = "" or db.callsign like '%input3%') OR

   (input4 = "" or db.callsign like '%input4%') "

Hi Jose,

Thanks for the reply. If I send a single query with 4 likes to the database performance will be quite poor. Do you have a solution which would not make use of a variable which is equal to "" at all?

I'm open to investigating more complicated solutions, I just don't know where to begin looking.

Thanks!

I think if the input is = "" it will not execute the like '%input%'.

You can use expand inline parameters creating the conditions as parameters.

Ok that's interesting. However, in your solution you are only calling db.callsign. What would it look like if I want to search each column?

Here's what I mean:

"where 

   (input1 = "" or db.callsign/db.ades/db.adep/db.fplid like '%input1%') OR

   (input2 = "" or db.callsign/db.ades/db.adep/db.fplid like '%input2%') OR

   (input3 = "" or db.callsign/db.ades/db.adep/db.fplid like '%input3%') OR

   (input4 = "" or db.callsign/db.ades/db.adep/db.fplid like '%input4%') "

What you could do is join those 4 columns together into 1 big calculated column and perform the like over that, but i'm not sure in what direction that is going to impact performance.

So something like (db.callsign + " " + db.ades + " " + db.adep + " " + db.fplid) like '%input1%'

The syntax depends on your database.

Search the 4 inputs per column, it will be costly to the db, you can create an index including all 4 columns.

(input1 = "" or db.callsign like '%input1%') OR

   (input2 = "" or db.callsign like '%input2%') OR

   (input3 = "" or db.callsign like '%input3%') OR

   (input4 = "" or db.callsign like '%input4%')  OR

(input1 = "" or db.ades like '%input1%') OR

   (input2 = "" or db.ades like '%input2%') OR

   (input3 = "" or db.ades like '%input3%') OR

   (input4 = "" or db.ades like '%input4%')  OR 

(input1 = "" or db.adep like '%input1%') OR

   (input2 = "" or db.adep like '%input2%') OR

   (input3 = "" or db.adep like '%input3%') OR

   (input4 = "" or db.adep like '%input4%')  OR 

(input1 = "" or db.fplid  like '%input1%') OR

   (input2 = "" or db.fplid  like '%input2%') OR

   (input3 = "" or db.fplid  like '%input3%') OR

   (input4 = "" or db.fplid  like '%input4%') 

Notice that if the input = "" it will cost zero to the query

The database is being used by other applications unfortunately. That would also affect performance because indexing these larger columns would be taxing for the database.

Thanks for the suggestion either way!

If you can, invest some time learning advanced SQL and indexing here:

https://www.brentozar.com/

Hi All,

as far as the filter condition is concerned, the one suggested above would return all records as soon as one of the 4 search fields is empty.  I think it would have to be more something like 

SomeEntityWithManyTextFields.Text1 like "%"+ SearchString1 +"%" and SearchString1 <> ""
or SomeEntityWithManyTextFields.Text1 like "%"+ SearchString2 +"%"  and SearchString2 <> ""
or SomeEntityWithManyTextFields.Text1 like "%"+ SearchString3 +"%"  and SearchString3 <> ""
or SomeEntityWithManyTextFields.Text1 like "%"+ SearchString4 +"%"  and SearchString4 <> ""
or
SomeEntityWithManyTextFields.Text2 like "%"+ SearchString1 +"%" and SearchString1 <> ""
or SomeEntityWithManyTextFields.Text2 like "%"+ SearchString2 +"%"  and SearchString2 <> ""
or SomeEntityWithManyTextFields.Text2 like "%"+ SearchString3 +"%"  and SearchString3 <> ""
or SomeEntityWithManyTextFields.Text2 like "%"+ SearchString4 +"%"  and SearchString4 <> ""
or 
SearchString1 = "" and  SearchString2 = "" and  SearchString3 = "" and  SearchString4 = ""

As far as performance is concerned, really depends, what do you mean by fairly large, thousands or millions of records ??

Dorine

Dorine is right, use <> instead of =, my bad :) 

Iam by far not a real SQL expert but i wonder if an UNPIVOT could no ease this kind of search. I created a custom sql query (outside of Outsystems) but i do not have the proper amount of testdata to give it a real try. Maybe it helps:

Edit: Parameter Checks  @param1 <> "" OR query_cte.Value = "%" + @param1 + "%" still applies :-)

Thank you for suggesting this Stefan.

I had never heard of UNPIVOT before, and gave it a try out of curiosity.  

It gives same results for a reasonably short bit of sql, i like it.  

I just joined outcome of the distinct[Id] with the full table to get all wanted rows.

Not sure about whether this performs particularly well, though.

If anyone is interested in a demo, see attached oml.


QDRLikeOnManyFields.oml

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