IG filter is great, but it only works with columns available in the query. Sometime we need to filter by related objects that are not part of the query, e.g. WHERE some reference EXISTS in another table. By the way, such components are available in ADF, RapidClips, etc.
Let say that I have to extend the standard search on EMP table with ability to find all employees that are listed as authors of some publications in PUB table.
- If I join EMP and PUB tables in IG query, then each employee will appear as many times as many publications he/she have, or I have to use DISTINCT that creates performance issues
- If I use additional page item or IG for filtering, then it will be not comfortable for users: most of filters are part of IG, other filters are in different regions, with different look and fill
- Of course, total replacing of IG filter functionality is also an option, but to keep the same look-and-feel, I should make this for all IGs in the application, which is a lot of work.
The best solution would be customization of the standard standard IG filter functionality.
If I add dummy column (e.g. Publication) and enable filtering for it, then I may select standard filter condition ( e.g. C, EQ, IN, etc.) and populate single or list of ISBN numbers (e.g. ISBN1, ISBN2) and press GO.
What I don’t know is how to customize the generation of SQL query. I see two possible ways to do so:
- somehow avoid using of this filter in the standard SQL generation and support it through a hidden page item
- somehow catch and modify the generated query (replace “Publication in (ISBN1, ISBN2)” with "emp_id in ( select emp_id form PUB where ISBN in (ISBN1, ISBN2) )"
I appreciate any kind of suggestion on this topic.