What is the most efficient way to store multiple dynamic filter which can be created by users on given data and us the filter for aggregating data?
Lets assume I have a table called t_filter which will store some data filter/queries for given data t_data.
The user can build each filter completely dynamic and can save it to create some kind of dashboard (list of the saved filter by filter_name and some aggregation function on the data which is filtered by the conditions).
Each filter can have different conditions and operators (>=, <=, !=, ==, IS NOT NULL, IS NULL, AND, OR, BETWEEN, etc.) on the different fields of given entries in t_data.
The user can modify its filter: create/update/delete and the entries in t_data can grow through imports or shrink through deletes, so everything is kind of dynamic.
I solved this problem by creating dynamic SQL based on the created filter from the user, UNION ALL every of the existing dynamic SQL entries in t_filter and creating a Materialized View out of the result (just the foreign key information: which filter_id belongs to which data_id).
This solution works but is not that handy because I need to check for filter changes and regenerate the Materialized View as well as for new or deleted entries in t_data.
For every additional filter entry the time to generate the View is taking longer because of the extra SQL which needs to be executed.
Is there a better way to solve that problem?
