Multi column search

Classic Servoy
1

Do you do a multi column search via find()/search() or via sql query?

I have a single global search field and I want to search the project_number (int) and project_name (string) columns for the value.

I know how to search via sql but cannot work out how to do it using find & search. Is one better than the other?

Thx

2

Hi James,

Yes you can seach on multiple columns in one search:

if ( controller.find() ) {
   column1 = globals.mySearchVar;
   column2 = globals.mySearchVar;
   controller.search();
}

This is the equivalent of the following SQL:

SELECT id FROM theTable WHERE column1=? AND column2=?

But I think you want to do an OR search. So the value matches in project number OR project name.
Then you code would be like this:

if ( controller.find() ) {
   column1 = globals.mySearchVar;
   controller.newRecord(); // new search criteria
   column2 = globals.mySearchVar;
   controller.search();
}

Which translates into SQL like so:

SELECT id FROM theTable WHERE column1=? OR column2=?

Also use %, ! and # where needed.

Hope this helps

3

Thanks, helps alot. Do I need to convert the integer (project_number) to a string to do a wildcard search?

4

4xjbh:
Thanks, helps alot. Do I need to convert the integer (project_number) to a string to do a wildcard search?

Hmm…I think you better use SQL then to perform the search. Since you need to cast the values in the database (thus the field) to be able to use wildcards. I don’t think you can otherwise use wildcards in integer data.

Pseudo SQL:

SELECT id FROM myTable WHERE cast(project_id, 'string') LIKE '%23456%' OR projectname LIKE '%23456%'

Depending on the database brand you need to change the cast function (I assume you use MSSQL or Sybase since you mentioned ‘convert’).

This SQL you can then use to load the foundset like so:

var _sQuery = "SELECT id FROM myTable WHERE cast(project_id, 'string') LIKE ? OR projectname LIKE ?"; // pseudo sql
var _sArg = "%" + globals.mySearchVar + "%";
controller.loadRecords(_sQuery, [_sArg, _sArg]);

Hope this helps.

5

Thanks,

just using the following worked fine.

var _sQuery = "SELECT project_id FROM project WHERE project_number LIKE ? OR project_name LIKE ?"; // pseudo sql
6

Interesting. Do you also use % in the searchstring on the int column ?

7

Yep. ‘%’ + globals.srch_main + ‘%’

Works a treat. …at the moment.