searching

Classic Servoy
1

I want to search in a table for the following:

username = “marcel”
AND
value = “foo 1”;

and I want to add to that foundset all records that hold:

value = “foo 2”

I now do this like:

controller.search();
username = "marcel";
value = "foo 1";
controller.newRecord();
value = "foo 2";
controller.find();

or is there another way?

2

what you suggest is possible by extending your search

controller.find();
username = "marcel"; 
value = "foo 1"; 
controller.search(); 
controller.find();
value = "foo 2"; 
controller.search(false,false); //extending foundset here

since rc6 it is possible to do a form by query:

controller.loadRecords("select <tablepk> from <table> where (<table>.username = 'marcel' and <table>.value = 'foo1') or <table>.value = 'foo2'")
3

Jan,

  1. would your first suggestion do a better (~faster) job?
  2. I prefer your second suggestion but I have the following issue: I also search on single dates and date ranges in that same query. I haven’t been able to find the right way to do a query on them however (http://forum.servoy.com/viewtopic.php?t=1383)
4
  1. safer,easier but slower (has todo 2 queries)
  2. especially by date columns use the ‘?’ in the SQL, sample:
controller.loadRecords("select <tablepk> from <table> where (<table>.username = 'marcel' and <table>.datecolumn > ?) or <table>.datecolumn < ?", new Array(startDate,endDate))

where startDate and endDate are dataproviders