pentahopentaho-ctoolsccc

Filter cached sqlJdbs query in Pentaho CE


I use sqlJdbs query as a data provider for my CCC controls. I use geospatial request in my query that's why I cache my results(Cache=True). Otherwise the request made long.

It works fine. However I have to use parameters in my query to filter resulting rows: SELECT ... FROM ... WHERE someField IN (${aoi_param})

Is there some way to cache full set of rows and then apply WHERE to cached results without rebuilding new cache for each set of values in the ${aoi_param}?

What is the best practice?


Solution

  • So, I am not really sure that it is the best practice, but I solved my problem this way:

    1. I included aoi_param to the Listeners and Parameters of my chart control

    2. Then I filtered data set in Post Fetch:

    function f(data){
        var _aoi_param = this.dashboard.getParameterValue('${p:aoi_param}');
        
        function isInArray(myValue, myArray) {
            var arrayLength = myArray.length;
            for (var i = 0; i < arrayLength; i++) {
                if (myValue == myArray[i]) return true;
            }
            return false;
        }
            
        function getFiltered(cdaData, filterArray) {
            var allCdaData = cdaData;
            cdaData = {
                metadata: allCdaData.metadata,
                resultset:  allCdaData.resultset.filter(function(row){
    					// 2nd column is an AOI id in my dataset
                        return isInArray(row[2], filterArray); 
                    })
            };
        
            return cdaData;
        }
        
        var dataFiltered = getFiltered(data, _aoi_param);
        return dataFiltered;
    }

    1. excluded WHERE someField IN (${aoi_param}) from the query of my sql over sqlJdbc component