Filtering on a related table

Classic Servoy
1

Hi,

I am trying to figure out how to use addTableFilterParam to filter on a related table.

My application is based around ‘cases’ and has a main record (case_header) that has many related tables to hold additional information (e.g. case_people, case_items, case_invoices etc) which are all linked to the case_header table via a case_header_id foreign key.

I want to allow people to log in to the system but see only those cases they involved with (a record exists for them in the case_people table linked to the case_header). All my forms are based on the case_header table and have tabs showing information from the related tables so I am trying to find an easy way to filter the case_header table so that only the correct cases are shown when the user logs in. I have looked at this post (https://www.servoy.com/forum/viewtopic.php?f=3&t=5864) which talks about using a nested in query but this doesn’t work for my scenario. What I really want is something like:

databaseManager.addRelatedTableFilterParam(serverName, tableName, relationName, dataProvider, operator, value, filterName);

The effect of this would be to adjust the generated SQL to either add an INNER JOIN on the columns specified in the relation plus the additional specified comparison or add an EXISTS element to the WHERE clause that does the same thing.

Obviously this doesn’t exist (anyone think it worthy of a feature request?) but can anyone point me in the direction of how I can achieve this without redesigning the record logic on all my forms?

Thanks
Steve

2

Will this work?

databaseManager.addTableFilterParam(serverName, 'case_header', 'case_header_pk', 'in', 'select case_header_fk from case_people where case_people_fk = yourPersonId')

I assumed that ‘case_people’ is a many_to_many table containing both foreign keys for the case and the person.
youPersonId can be stored in a global variable upon the user login.

With the table filter you won’t be able to see any other cases in your foundset in the code, so if you still need to manipulate other users’ records, you might use foundset.addFoundSetFilterParam(dataprovider,operator,value) - not sure if the dataprovider can be in a related table though :confused:

Alternatively or if none of the above works, you might create an underlying form based on your people table and put your form with cases on top of it through a relation ‘people_to_case_people’. On startup, select the person, who logged in, in that people form and it will show you all cases that only relate to them.

3

Hi Maria,

Thanks for the suggestion and it does exactly what I need though at the cost of some performance as the IN clause is never the fastest option! I’ll submit a feature request for my original idea and see if it is picked up :)

THanks
Steve

4

Hi Steve,

+5 for this feature request !

Great performance improvement with (related) tables with 100000 records +

Regards,

5

Hi Lambert,

I have logged it as Jira.

Please vote for it there :D

Thanks
Steve

6

Hi Steve,

I did, thanks for bringing this up.

Regards,