Extend Found Set done by SQL Search

Classic Servoy
1

I have a search field on a form for users to enter a name to search for. This search field is a global variable. I’m using getDataSetByQuery to query the database, but i’m stuck on how to append (aka Extend) the search.

TABLE NAME: employee_mass_spec
FIELD NAME: first_name
GLOBAL: Search_Employee

var search = globals.Search_Employee.split (" ")
var maxrows = 1000 

for (var i=0; i<=search.length ; i++)
{
	var lookup = "'%" + search[i] + "%'" 
	var query = 'SELECT emplid FROM employee_mass_spec WHERE first_name LIKE ' + lookup;
	var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxrows);
		if (i!=search.length)
		{
		WHAT GOES HERE TO APPEND TO THE SEARCH?
		}
		
}
controller.loadRecords(dataset)

I’m stuck on what should be placed in the nested IF statement to keep this search going and just append the records.

Any help out there?

Thank you,
David

2

If I get you right, your users can enter several names? If yes, try this:

var search = globals.Search_Employee.split (" ");
var maxrows = 1000;
var query = 'SELECT employee_mass_spec.emplid FROM employee_mass_spec WHERE ';

for (var i=0; i<=search.length ; i++)
{
   var lookup = "'%" + search[i] + "%'"
   if (i == 0) {
      // the first criteria doesn't need AND or OR
      query += 'employee_mass_spec.first_name LIKE ' + lookup;
   }
   else {
      // do you need AND or OR?
      query += ' OR employee_mass_spec.first_name LIKE ' + lookup;
   }
query += ' ORDER BY employee_mass_spec.emplid';
controller.loadRecords(query )

Does this help?

3

You could do it without a loop:

var aSearch = globals.Search_Employee.split (" "); // get every word
var sQuery = 'SELECT employee_mass_spec.emplid FROM employee_mass_spec WHERE ';
var sSearch = aSearch.join("%' OR first_name LIKE '%"); // join array with this string into String object
sQuery += "first_name LIKE '%" + sSearch + "%' "; // Add the first and last part of the where clause
sQuery += 'ORDER BY employee_mass_spec.emplid;'; 
controller.loadRecords(sQuery);

Hope this helps.

4

Thank you both!

Robert, how would I append your code to also look in the field last_name?

So the search would look in two fields - first_name & last_name?

5

How about this:

var sQuery = 'SELECT employee_mass_spec.emplid FROM employee_mass_spec WHERE ';
var sSearchFirst = aSearch.join("%' OR employee_mass_spec.first_name LIKE '%");
var sSearchLast = aSearch.join("%' OR employee_mass_spec.last_name LIKE '%");
sQuery += "employee_mass_spec.first_name LIKE '%" + sSearchFirst + "%' OR employee_mass_spec.last_name LIKE '%" + sSearchLast + "%'";
6

davidaarong:
Robert, how would I append your code to also look in the field last_name?

So the search would look in two fields - first_name & last_name?

As Patrick showed you. Just reuse the array.

7

ok, i think i’m getting ahead of myself.

How would I create a search from a global that a user can enter a full name (e.g. JOHN SMITH) and have it match against two fields first_name & last_name?

I know I owe both of you a drink after this…

8

nevermind, i just modified the following:

var search = globals.Search_Employee.split (" ")
var maxrows = 1000 
var query = 'SELECT employee_mass_spec.emplid FROM employee_mass_spec WHERE ';

for (var i=0; i<=search.length ; i++)
{

	var lookup = "'%" + search[i] + "%'" 
			if (i == 0)
			{ 
					// the first criteria doesn't need AND or OR 
      				query += 'employee_mass_spec.first_name LIKE ' + lookup; 
   			}			 
		   	else if (i!=search.length)
			{ 
      				// do you need AND or OR?
      				query += 'AND employee_mass_spec.last_name LIKE ' + lookup;
   			}
			 
}
query += ' ORDER BY employee_mass_spec.emplid'; 
controller.loadRecords(query )

[/code]

9

Once again, thank you both for your time and help. It’s greatly appreciated.

10

Hi David,
I see you use the following bit of code;```
else if (i!=search.length)

I am sure you put that in there because you kept getting an error.
This error is in the following line of code:```
for (var i=0; i<=search.length ; i++)

An array is zero based. So the first value in an array is addressed by position zero.
The length function however returns the actually number of values in an array. So lets say you have 2 values in an array you have position 0 and 1.
Having the ‘i<=search.length’ bit in the offending code will make the variable i go passed the amount of positions in the array.

So your code should look like this:

var search = globals.Search_Employee.split (" ");
var maxrows = 1000; //  Btw, where is this used??
var query = 'SELECT employee_mass_spec.emplid FROM employee_mass_spec WHERE ';
var lookup = ""; // Better declare it here once then many times in the loop, declaring variables is costly

for (var i=0; i<search.length ; i++)
{
	lookup = "'%" + search[i] + "%'" 
	if (i == 0)
	{ 
		query += 'employee_mass_spec.first_name LIKE ' + lookup; 
	} else { 
		query += 'AND employee_mass_spec.last_name LIKE ' + lookup;
	}		 
}
query += ' ORDER BY employee_mass_spec.emplid;'; 
controller.loadRecords(query)

Hope this helps.

11

Hi Robert,

Yes, that does help and thank you for the tip about not declaring the variable inside of the loop.

  • David