Performance of addTableFilterParam with IN operator

[Bernd.N]

We need to show users only part of the data, e.g. a department manager may only see the projects from her own department.

That can be efficiently done with
databaseManager.addTableFilterParam(scopes.utils.DB.SERVER, 'projects', 'department_id', '=', scopes.person.currentUserDepartmentID, 'projectsFilter');

Unfortunately, that department manager sometimes is the project leader of a project that runs in another department. So she needs access to that project, too.
With the table filter above, this can not be done.

A solution is to create an array _aProjects of all PKs of the project records where the user needs access. Then the filter condition is like this:
databaseManager.addTableFilterParam(scopes.utils.DB.SERVER, 'projects', 'project_id', 'in', _aProjects, 'projectsFilter');

This solution seems to have the disadvantage that it slows down database operations considerably, e.g. the saving of a project element needs now 45 seconds instead of 2 seconds.
I have to admit that the number of projects in the _aProjects array is quite large for some department managers, as we took over 10 years of old data.

My current idea is to reduce the size of _aProjects to only those records that are currently running (open), so that all 10.000 old projects from the past do not appear in the list any more, and do not cause bad performance.

Did anybody have a similar situation in filtering data?
I would be interested if there are other solutions or ideas than the "project_id IN array" - filtering which seems to slow down the server a lot, when the array is large.

One idea: Is there a way to connect several addTableFilterParam with "OR" ?
It should result in this filtering operation of Servoy:
(department_id = scopes.person.currentUserDepartmentID OR department_id IN <very small list of additional PKs>)

You can issue several addTableFilterParam for one table, but as far as I know they are then always connected with AND.

[patrick]

How about a join that sees a projects a manager has access to? something like

databaseManager.addTableFilterParam(scopes.utils.DB.SERVER, 'projects', 'department_id', 'IN', 'SELECT department_id FROM departments WHERE manager_id = 123', 'projectsFilter');

[rgansevles]

Maybe you can first determine all depoartments that belong to the projects and filter on those departments only, those are probably fewer.

I guess what would be nice here to have support for QueryBuilder in table filter, something like:

var query = datasources.db.mydb.departments.createSelect()
query.result.add(...)
query.where.add(...)

databaseManager.addTableFilterParam('mydb', departments, 'departmentid', 'in', query)

Rob

[Bernd.N]

Hello,
the problem is not that we need to filter several departments for one department manager.
That could indeed be done with the solution that Patrick suggested.

We need to filter on the base of different fields in the project record.
Sorry that I made that not clear in my first posting.

So the department leader should not see all projects from both department A and department B.
He should see all projects from his department A, plus some other projects where he is the project leader.

So we need a filtering expression for addTableFilterParam like this:
(department_id = scopes.person.currentUserDepartmentID OR project_manager_id = scopes.person.currentUserID)

Is it possible to configure addTableFilterParam in a way that it will do this filtering with that needed OR-condition?

[patrick]

As long as you can write any query that will return all project_ids that a user has access to, you could use my approach with the IN clause. So something like

var projectIdQuery = 'SELECT project_id FROM projects WHERE department_id = scopes.person.currentUserDepartmentID OR project_manager_id = scopes.person.currentUserID'

could be used in

databaseManager.addTableFilterParam(scopes.utils.DB.SERVER, 'projects', 'project_id', 'IN', projectIdQuery, 'projectsFilter')

(Note that I'm now filtering on project_id, since it's not only about departments anymore)

[Bernd.N]

That could be a solution which takes away our performance problems.

Because currently we create an array _aProjects and use the IN-operator on that array
databaseManager.addTableFilterParam(scopes.utils.DB.SERVER, 'projects', 'project_id', 'IN', _aProjects, 'projectsFilter');

Your solution should be much better as then Servoy would not need to hold a large list of 10.000 projectIDs, but could run an optimized query based on your projectIdQuery - where we have indexes on department_id and project_manager_id to make it fast.

Thanks very much for that idea, we will give it a try.

[Bernd.N]

I can confirm that the 45-second-bottleneck was caused by
databaseManager.addTableFilterParam(scopes.utils.DB.SERVER, 'projects', 'project_id', 'IN', _aProjects, 'projectsFilter');
where the _aProjects was a large array with thousands of IDs.

This bottleneck vanished as soon as I changed it to Patrick's suggestion with "IN sqlQuery".

Thanks to all who helped!
Bernd