searching

[IT2Be]

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:

Code: Select all

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

or is there another way?

[Jan Blok]

what you suggest is possible by extending your search

Code: Select all

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:

Code: Select all

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

[IT2Be]

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)

[Jan Blok]

1) safer,easier but slower (has todo 2 queries)
2) especially by date columns use the '?' in the SQL, sample:

Code: Select all

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