如何在Google Charts API中实现两个关联的Category Filter

我需要创建关联的State和City两个Category Filter，选择State过滤器时，要同时过滤City过滤器和对应的两张图表。

已构建两个DataTable：

State DataTable #

City DataTable #

我参考过相关问题，但数据格式不同，不清楚如何用setChartView()、绘制图表，以及在google.visualization.events.addListener()中实现关联逻辑。以下是我已编写的代码片段：

构建列选择表的代码 #

var stcolumnTable = new google.visualization.DataTable();
stcolumnTable.addColumn('number','colIndex');
stcolumnTable.addColumn('string','colLabel');
var st_initState={st_selectedValues:[]};

var cycolumnTable = new google.visualization.DataTable();
cycolumnTable .addColumn('number','colIndex');
cycolumnTable .addColumn('string','colLabel');
var cy_initState={cy_selectedValues:[]};

for(var i=1 ; i < state_dtbl.getNumberOfColumns(); i++){
   columnTable.addRow([i,state_dtbl.getColumnLabel(i)]);
   initState.selectedValues.push(state_dtbl.getColumnLabel(i));
};

for(var i=1 ; i < city_dtbl.getNumberOfColumns(); i++){
   columnTable.addRow([i,city_dtbl.getColumnLabel(i)]);
   cy_initState.cy_selectedValues.push(city_dtbl.getColumnLabel(i));
};

初始化Category Filter的代码 #

var statepicker = new google.visualization.ControlWrapper({
        controlType: 'CategoryFilter',
        containerId: 'state_div',
        dataTable: stcolumnTable,
        options: {
          filterColumnLabel: 'colLabel',
          ui: {
            labelStacking: 'vertical',
            allowTyping: false,
            allowMultiple: true
            }
          },
    // 初始选中所有州
    state: {'selectedValues': ['ST1', 'ST2']}
    });  

var citypicker = new google.visualization.ControlWrapper({
        controlType: 'CategoryFilter',
        containerId: 'city_div',
        dataTable: cycolumnTable,
        options: {
          filterColumnLabel: 'colLabel',
          ui: {
            labelStacking: 'vertical',
            allowTyping: false,
            allowMultiple: true
            }
          },
    // 初始选中所有城市
    state: {'selectedValues': ['ST1~ NY', 'ST1~ NJ']}
    });

以下是实现关联过滤和图表绘制的完整代码，包含事件监听、视图更新逻辑：

<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['corechart', 'controls']});
      google.charts.setOnLoadCallback(drawDashboard);

      function drawDashboard() {
        // 构建State DataTable
        var state_dtbl = new google.visualization.DataTable();
        state_dtbl.addColumn('string', 'Date');
        state_dtbl.addColumn('number', 'ST1');
        state_dtbl.addColumn('number', 'ST2');
        state_dtbl.addRows([
          ['Jan-2024', 100, 150],
          ['Feb-2024', 200, 250],
          ['Mar-2024', 300, 350],
          ['Apr-2024', 400, 450],
          ['May-2024', 500, 550]
        ]);

        // 构建City DataTable
        var city_dtbl = new google.visualization.DataTable();
        city_dtbl.addColumn('string', 'Date');
        city_dtbl.addColumn('number', 'ST1~ NY');
        city_dtbl.addColumn('number', 'ST1~ NJ');
        city_dtbl.addRows([
          ['Jan-2024', 50, 50],
          ['Feb-2024', 100, 100],
          ['Mar-2024', 200, 100],
          ['Apr-2024', 150, 250],
          ['May-2024', 300, 200]
        ]);

        // 构建State过滤器的列选择表
        var stcolumnTable = new google.visualization.DataTable();
        stcolumnTable.addColumn('number','colIndex');
        stcolumnTable.addColumn('string','colLabel');
        var st_initState = {selectedValues: []};
        for(var i=1 ; i < state_dtbl.getNumberOfColumns(); i++){
           stcolumnTable.addRow([i, state_dtbl.getColumnLabel(i)]);
           st_initState.selectedValues.push(state_dtbl.getColumnLabel(i));
        };

        // 构建City过滤器的列选择表
        var cycolumnTable = new google.visualization.DataTable();
        cycolumnTable.addColumn('number','colIndex');
        cycolumnTable.addColumn('string','colLabel');
        var cy_initState = {selectedValues: []};
        for(var i=1 ; i < city_dtbl.getNumberOfColumns(); i++){
           cycolumnTable.addRow([i, city_dtbl.getColumnLabel(i)]);
           cy_initState.selectedValues.push(city_dtbl.getColumnLabel(i));
        };

        // 初始化State过滤器
        var statepicker = new google.visualization.ControlWrapper({
          controlType: 'CategoryFilter',
          containerId: 'state_div',
          dataTable: stcolumnTable,
          options: {
            filterColumnLabel: 'colLabel',
            ui: {
              labelStacking: 'vertical',
              allowTyping: false,
              allowMultiple: true,
              label: '选择州:'
            }
          },
          state: st_initState
        });  

        // 初始化City过滤器
        var citypicker = new google.visualization.ControlWrapper({
          controlType: 'CategoryFilter',
          containerId: 'city_div',
          dataTable: cycolumnTable,
          options: {
            filterColumnLabel: 'colLabel',
            ui: {
              labelStacking: 'vertical',
              allowTyping: false,
              allowMultiple: true,
              label: '选择城市:'
            }
          },
          state: cy_initState
        });

        // 初始化State图表
        var stateChart = new google.visualization.ChartWrapper({
          chartType: 'ColumnChart',
          containerId: 'state_chart_div',
          dataTable: state_dtbl,
          options: {
            title: '州数据统计',
            width: 600,
            height: 400
          }
        });

        // 初始化City图表
        var cityChart = new google.visualization.ChartWrapper({
          chartType: 'ColumnChart',
          containerId: 'city_chart_div',
          dataTable: city_dtbl,
          options: {
            title: '城市数据统计',
            width: 600,
            height: 400
          }
        });

        // 监听State过滤器的选择变化
        google.visualization.events.addListener(statepicker, 'statechange', function() {
          var selectedStates = statepicker.getState().selectedValues;
          
          // 更新City过滤器的可选选项：只显示选中州对应的城市
          var allowedCities = [];
          for(var i=1; i < city_dtbl.getNumberOfColumns(); i++){
            var cityLabel = city_dtbl.getColumnLabel(i);
            // 提取城市标签中的州前缀（比如ST1~ NY中的ST1）
            var statePrefix = cityLabel.split('~')[0].trim();
            if(selectedStates.indexOf(statePrefix) !== -1){
              allowedCities.push(cityLabel);
            }
          }

          // 更新City过滤器的视图，只保留允许的城市
          citypicker.setOption('ui.allowableValues', allowedCities);
          
          // 如果当前选中的城市不在允许列表中，重置选中状态为所有允许的城市
          var currentCitySelections = citypicker.getState().selectedValues;
          var validSelections = currentCitySelections.filter(city => allowedCities.includes(city));
          if(validSelections.length === 0){
            validSelections = allowedCities;
          }
          citypicker.setState({selectedValues: validSelections});
          citypicker.draw();

          // 更新State图表的视图：只显示选中的州列
          var stateViewColumns = [0]; // 保留Date列
          selectedStates.forEach(function(state){
            for(var i=1; i < state_dtbl.getNumberOfColumns(); i++){
              if(state_dtbl.getColumnLabel(i) === state){
                stateViewColumns.push(i);
                break;
              }
            }
          });
          stateChart.setView({columns: stateViewColumns});
          stateChart.draw();
        });

        // 监听City过滤器的选择变化，更新City图表
        google.visualization.events.addListener(citypicker, 'statechange', function() {
          var selectedCities = citypicker.getState().selectedValues;
          
          // 更新City图表的视图：只显示选中的城市列
          var cityViewColumns = [0]; // 保留Date列
          selectedCities.forEach(function(city){
            for(var i=1; i < city_dtbl.getNumberOfColumns(); i++){
              if(city_dtbl.getColumnLabel(i) === city){
                cityViewColumns.push(i);
                break;
              }
            }
          });
          cityChart.setView({columns: cityViewColumns});
          cityChart.draw();
        });

        // 绘制所有控件和图表
        statepicker.draw();
        citypicker.draw();
        stateChart.draw();
        cityChart.draw();
      }
    </script>
  </head>
  <body>
    <div id="state_div"></div>
    <div id="city_div"></div>
    <div id="state_chart_div"></div>
    <div id="city_chart_div"></div>
  </body>
</html>

关键逻辑说明 #

• State过滤器监听：当选中州时，提取城市标签中的州前缀，筛选出属于选中州的城市，更新City过滤器的可选列表和选中状态。
• 视图更新：通过setView({columns: ...})方法，只保留选中的列来更新图表显示。
• City过滤器监听：选中城市后，直接更新City图表的列视图，只显示选中的城市数据。

内容的提问来源于stack exchange，提问作者Curious_learner