filter wrong expression?

Classic Servoy
1

I’m using as a filter,

tempText = “SELECT student_service.svc_student_id FROM student_service WHERE student_service.svc_teacher_id = '” + globals.currentTeacher_id + “'”
tempText += " AND student_service.svc_start_d::date <= '" + asOfDateString + “'”
tempText += " AND ((student_service.svc_drop_d::date IS NULL) OR (student_service.svc_drop_d::date >= '" + asOfDateString + “'))”
success_B = forms.student_browse.foundset.addFoundSetFilterParam(‘std_student_id’, “IN”, tempText, ‘student_browse_teach_rstrs’);

When I log in as this teacher, I should see only those students for whom s/he provides active services. But I’m seeing (among others) one student who does not have any services with this teacher.

The performance data section shows,

select std_student_id from student where std_student_id = ? and std_student_id in (SELECT student_service.svc_student_id FROM student_service WHERE student_service.svc_teacher_id = ‘C1A12860-EB19-4E7D-B4C3-D023B8953F72’ AND student_service.svc_start_d::date <= ‘2012-6-11’ AND ((student_service.svc_drop_d::date IS NULL) OR (student_service.svc_drop_d::date >= ‘2012-6-11’))) and tenant_id = ? order by std_student_id asc limit ?

Is the filter incorrectly formed?

Thank you,
Don

2

Don,

The filter seems OK to me, also the sql generated by Servoy seems to apply the filter correctly.
If you run this sql directly against the database, do you see the unexpected student ids?

Maybe the filter is not used on all foundsets on the student table.

As an alternative approach you could also look at databaseManager.convertFoundSet().
If you create a relation student_service_to_student (on studentid) you can use a filtered student_service foundset and create a foundset on the student table with all students for that student service:

var fs = databaseManager.getFoundSet('db:/server/student_service')
if (fs.find()
{
  fs.svc_teacher_id = globals.currentTeacher_id
  svc_start_d = '<='+asOfDateString // may need dateformat
  svc_drop_d = '^>='+asOfDateString // may need dateformat
  fs.search()
  student_fs = databaseManager.convertFoundSet(fs, 'student_service_to_student')
}

Rob

3

Hi Rob,

I ran it as a separate sql statement (without the filters active) and it performed the same way. But it looks like I missed a related record in the “fog of programming”. So I think my filter is okay.

With the converted foundset approach, wouldn’t I need to apply that each time the user performed a search? If the user performed a search of the students, how would I then apply the converted foundset to further restrict the results of the search? Sort of like an SQL intersect?

Thank you,
Don