I understand that if I have a SELECT statement with a clause “WHERE AGE = 10”, I just need to place foundset.age = 10 and insert it in the code below.
001 foundset.find()
002 [here]
003 foundset.search()
But if I have the following SELECT statements, how will I code it in Servoy without coding the traditional SELECT statement?
SELECT * FROM STUDENT WHERE:
A. FIRST_NAME LIKE ‘A%’
B. GRADES > 98.5
C. WHERE AGE BETWEEN 12 AND 20
Is this possible? Thanks.
SELECT * FROM STUDENT WHERE:
A. FIRST_NAME LIKE ‘A%’
B. GRADES > 98.5
C. WHERE AGE BETWEEN 12 AND 20
out of my head:
if(controller.find()) {
first_name = 'a%'
grades > '98.5'
age = '12...15'
var count = controller.search()
}
This is not correct:
grades > ‘98.5’
It needs to be:
grades = ‘>98.5’
Have a look here for the entire Find/Search syntax: http://wiki.servoy.com/display/DOCS/JSF … ndset-find
Paul
ah yes, you are right, as I said, out of my head…
Thanks for this.
How about (Table1.Field1=1 or (Table1.Field1=2 and Table2.Field1=3) )?
How do i display this through Servoy foundset search?
[Added] Can Servoy allow nested multiple AND/OR conditions with parenthesis?
To use OR in your where clause, you set the first parameters inside a find(), then call foundset.newRecord() and add the OR parameters to this new record.
So your example would be (provided that foundset is based on Table1 and Table2 is bound to it by a relation tb1_to_tb2):
if (foundset.find()) {
field1 = 1;
foundset.newRecord();
field1 = 2;
tb1_to_tb2.field1 = 3;
var count = foundset.search();
if (count) {
// do something with the foundset
}
}
Thanks for this.
But I am still confused.
If I have (Rel1.Field1=1 OR Rel2.Field2=2) AND (Rel3.Field3 OR Rel4.Field4), would the code be like:
if (foundset.find()) {
foundset.Rel1.Field1=1;
foundset.newRecord();
foundset.Rel2.Field2=2;
<What do I place here?>
foundset.Rel3.Field3 =3;
foundset.newRecord();
foundset.Rel4.Field4=4;
var count = foundset.search();
if (count) {
// do something with the foundset
}
}
How do I show the parenthesis to denote priority?
Erik,
That is not supported directly (yet).
Setting multiple fields in find mode implies AND, using using multiple records implies OR.
Additionally, you can use foundset.search(clearLastResults=false, reduceSearch=true for AND and false for OR)
You can do this:
if (foundset.find()) {
foundset.Rel1.Field1=1;
foundset.newRecord();
foundset.Rel2.Field2=2;
foundset.search()
foundset.find()
foundset.Rel3.Field3 =3;
foundset.newRecord();
foundset.Rel4.Field4=4;
var count = foundset.search(false, true);
if (count) {
// do something with the foundset
}
}
The drawback is that you have an intermediate query (the search() call).
Rob
My query is dynamic and is created during runtime. It may have nested brackets with endless levels with different combinations of OR and AND.
I will just use SQL SELECT statements to resolve this.
Thanks for the reply.
Hi, will nested OR, AND and brackets be supported in foundsets for Servoy 6? If this would not be supported in Servoy 6, I have no option but to use native SQL statements. However, I encountered a road block when I use non-stored field calculations in my SQL WHERE statements. SQL statements cannot identify fields which are not physically in the database. Is there a way to solve this problem without changing our non-stored calculations to stored calculations?
Hi Erik,
Using either Servoy objects or SQL you can’t search on non-stored calculations. The reason for this is that Servoy will translate your search into SQL and send that to the database server. So Servoy itself doesn’t do any searching, the database server does and that database server has no clue about non-stored calculation columns.
The only workaround is (other than making it a stored calc) to put the logic of your non-stored calc in your SQL.
Hope this helps.
ROCLASI:
Hi Erik,Using either Servoy objects or SQL you can’t search on non-stored calculations. The reason for this is that Servoy will translate your search into SQL and send that to the database server. So Servoy itself doesn’t do any searching, the database server does and that database server has no clue about non-stored calculation columns.
The only workaround is (other than making it a stored calc) to put the logic of your non-stored calc in your SQL.Hope this helps.
I understand. That’s what I did, I tried copying the calculation functionality by making JOINS and other operations in SQL statements but this is not a good idea because if the calculation in Servoy changes I need to update the SQL logics too, which is very complex.
Thank you very much for your input on this. The information you gave is very helpful.
What about a date field. How do I search for that like this:
foundset.start_date = “>2011-10-01”;
This example doesn’t work. How do I do it then?
try this
foundset.start_date = ">2011-10-01|yyyy-MM-dd";
Hi, it’s not working any other solution?
Can you post your whole method?
Harjo:
foundset.start_date = ">2011-10-01|yyyy-MM-dd";
I believe that should be
foundset.start_date = "#>2011-10-01|yyyy-MM-dd";
I.e. add a hash in front of the search string.
Hope this helps.
Still not working This is all code that does the search. All the other conditions work. The date conditions are in the middle. I want to check if a booking overlaps with the selected interval. I’ve debug and the selected fields are correct (I was using them also before but inside a relation, which wasn’t too good because there I can’t search using OR, but only with AND).
/**
* @properties={typeid:24,uuid:"6DE79006-C68F-47F7-A1AF-DB7AB9226BB9"}
*/
function addPilotFullNameCondition(foundSet, searchText) {
if (searchText != "") {
foundSet.bookings_to_contacts.full_name = '#%' + searchText + '%';
}
}
/**
* @properties={typeid:24,uuid:"7B784BCC-0980-46A7-913B-DDE3B2A9C7D4"}
*/
function addInstructorFullNameCondition(foundSet, searchText) {
foundSet.bookings_to_instructor_contacts.full_name = '#%' + searchText + '%';
}
/**
* @properties={typeid:24,uuid:"C1F1132F-1863-4612-A676-E627B68E05C7"}
*/
function addCustomerFullNameCondition(foundSet, searchText) {
foundSet.bookings_to_customer_contacts.full_name = '#%' + searchText + '%';
}
/**
* @properties={typeid:24,uuid:"7A69745C-3C86-430A-8B57-545552ADEDB5"}
*/
function addPlaneCondition(foundSet) {
if (globals.vReservationsSelectedPlane > 0) {
foundSet.plane_id = globals.vReservationsSelectedPlane;
}
}
/**
* @properties={typeid:24,uuid:"BF9A1409-F5E9-4295-B913-F2083D77444F"}
*/
function addDateRangeCondition1(foundSet) {
foundSet.booking_start = "#>=" + utils.dateFormat(globals.vReservationsSelectedDate, "yyyy-MM-dd") + "|yyyy-MM-dd";
foundSet.booking_start = "#<=" + utils.dateFormat(globals.vReservationsSelectedDateEnd, "yyyy-MM-dd") + "|yyyy-MM-dd";
}
/**
* @properties={typeid:24,uuid:"F05F0C30-8C63-42D4-8B10-792E57D775CA"}
*/
function addDateRangeCondition2(foundSet) {
foundSet.booking_end = "#>=" + utils.dateFormat(globals.vReservationsSelectedDate, "yyyy-MM-dd") + "|yyyy-MM-dd";
foundSet.booking_end = "#<=" + utils.dateFormat(globals.vReservationsSelectedDateEnd, "yyyy-MM-dd") + "|yyyy-MM-dd";
}
/**
* @properties={typeid:24,uuid:"B208A83F-9817-481D-B2E7-92443AF19E7B"}
*/
function addDateRangeCondition3(foundSet) {
foundSet.booking_start = "#<=" + utils.dateFormat(globals.vReservationsSelectedDate, "yyyy-MM-dd") + "|yyyy-MM-dd";
foundSet.booking_end = "#>=" + utils.dateFormat(globals.vReservationsSelectedDateEnd, "yyyy-MM-dd") + "|yyyy-MM-dd";
}
/**
* @properties={typeid:24,uuid:"7D797769-FFCB-47CD-8CEC-CD786D39E0AB"}
*/
function filterReservationsData(foundSet) {
application.output("filterReservationsData");
var searchText = forms.search_box.getSearchText();
if (searchText == "" && globals.vReservationsSelectedPlane == 0) { // All reservations
if (foundSet.find()) {
addDateRangeCondition1(foundSet);
foundSet.newRecord();
addDateRangeCondition2(foundSet);
foundSet.newRecord();
addDateRangeCondition3(foundSet);
foundSet.search(true, true);
}
} else {
if (foundSet.find()) {
// search for pilot
addPilotFullNameCondition(foundSet, searchText);
addPlaneCondition(foundSet);
addDateRangeCondition1(foundSet);
foundSet.newRecord();
addPilotFullNameCondition(foundSet, searchText);
addPlaneCondition(foundSet);
addDateRangeCondition2(foundSet);
foundSet.newRecord();
addPilotFullNameCondition(foundSet, searchText);
addPlaneCondition(foundSet);
addDateRangeCondition3(foundSet);
// search for instructor
foundSet.newRecord();
addInstructorFullNameCondition(foundSet, searchText);
addPlaneCondition(foundSet);
addDateRangeCondition1(foundSet);
foundSet.newRecord();
addInstructorFullNameCondition(foundSet, searchText);
addPlaneCondition(foundSet);
addDateRangeCondition2(foundSet);
foundSet.newRecord();
addInstructorFullNameCondition(foundSet, searchText);
addPlaneCondition(foundSet);
addDateRangeCondition3(foundSet);
// search for customer
foundSet.newRecord();
addCustomerFullNameCondition(foundSet, searchText);
addPlaneCondition(foundSet);
addDateRangeCondition1(foundSet);
foundSet.newRecord();
addCustomerFullNameCondition(foundSet, searchText);
addPlaneCondition(foundSet);
addDateRangeCondition2(foundSet);
foundSet.newRecord();
addCustomerFullNameCondition(foundSet, searchText);
addPlaneCondition(foundSet);
addDateRangeCondition3(foundSet);
foundSet.search(true, true);
}
}
}
Hi Bogdan,
For date ranges (and number ranges) you can use the … operator in Servoy.
Servoy translates that to the BETWEEN sql statement.
So your code would be like this:
function addDateRangeCondition1(foundSet) {
// yes this is one single line of code
foundSet.booking_start = "#" + utils.dateFormat(globals.vReservationsSelectedDate, "yyyy-MM-dd") + "..." + utils.dateFormat(globals.vReservationsSelectedDateEnd, "yyyy-MM-dd") + "|yyyy-MM-dd";
}
Hope this helps