Wednesday, June 24, 2015

Intercepting Table Filter Query and Manipulating VO SQL Statement

I’m going to describe one non declarative use case. Imagine, if there is a table with filter functionality, you may want to intercept filter items and apply the same for another VO. This another VO should be based on the same DB table, so it could apply criteria items against the table.

Sample application - AdvancedViewCriteriaApp.zip, implements a fragment with table component and a chart. Table component can be filtered, criteria is intercepted and applied for the chart, this one is rendered from different VO with GROUP BY query. Chart stays in synch and displays data according to the criteria filtered in the table:


In the log, I’m printing out intercepted criteria from the table filter:


Chart is rendered from the SQL query below:


Table filter criteria is being intercepted by overridden method buildViewCriteriaClauses. Criteria clause is constructed here, we just need select FilterViewCriteria, the one originating from table filter. We could apply this criteria straight ahead to the VO responsible to bring chart data. However, this would not work - ADF BC would wrap original chart SQL query with SELECT * FROM (…) QRSLT WHERE (table filter criteria). This would not work, because table filter criteria is not present in the original chart SQL statement. To make it work, I’m updating original SQL query for chart data, by updating WHERE clause part:


In the last step, we need to pass bind variable values - the ones user is searching for in table filter. This can be done from another overridden method - bindParametersForCollection. We have access to the applied bind variables in this method. Again, you should check for FilterViewCriteria and extract applied bind variables values. Chart VO will be updated with bind variable definitions created on the fly and assigned with values to search for:


I hope this trick will save some of your time, if you are going to implement something similar - to intercept table filter query and apply it to the another VO, based on same DB table.

No comments: