Filtering on a related table

Questions, tips and tricks and techniques for scripting in Servoy

Filtering on a related table

Postby steve1376656734 » Tue Aug 05, 2014 1:39 pm

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:

Code: Select all
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
Steve
SAN Developer
steve1376656734
 
Posts: 180
Joined: Fri Aug 16, 2013 2:38 pm
Location: Lanzarote

Re: Filtering on a related table

Postby maria.kyselova » Tue Aug 12, 2014 6:01 am

Will this work?

Code: Select all
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 :/

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.
Cheers,
Maria
maria.kyselova
 
Posts: 172
Joined: Thu Aug 09, 2012 12:56 am

Re: Filtering on a related table

Postby steve1376656734 » Sun Aug 17, 2014 5:02 pm

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
Steve
SAN Developer
steve1376656734
 
Posts: 180
Joined: Fri Aug 16, 2013 2:38 pm
Location: Lanzarote

Re: Filtering on a related table

Postby lwjwillemsen » Mon Aug 18, 2014 8:48 am

Hi Steve,

+5 for this feature request !

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

Regards,
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 572
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: Filtering on a related table

Postby steve1376656734 » Mon Aug 18, 2014 8:53 am

Hi Lambert,

I have logged it as https://support.servoy.com/browse/SVY-7063.

Please vote for it there :D

Thanks
Steve
Steve
SAN Developer
steve1376656734
 
Posts: 180
Joined: Fri Aug 16, 2013 2:38 pm
Location: Lanzarote

Re: Filtering on a related table

Postby lwjwillemsen » Mon Aug 18, 2014 10:13 am

Hi Steve,

I did, thanks for bringing this up.

Regards,
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 572
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands


Return to Methods

Who is online

Users browsing this forum: No registered users and 2 guests

cron