Filter cached sqlJdbs query in Pentaho CE

Question

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?

Answer 1

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;
}

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