How do I get a Google visualization Table to sort

问题:

I'm using numerous Google visualization Tables to display a variety of data in a single-paged, multi-tabbed web app. Some of these tables have columns that use formatted values. When a user clicks on the column header for these columns, the data is sorted according to the sort order of the underlying values rather than the sort order of the formatted values. This results in some cases in the data not appearing to the user to be sorted.

I'm looking for a way to fix this that can be reused across all my Tables, preferably by writing one event listener that can be attached to each Table and will handle all of the specifics regardless of the data types used in the various columns of the various associated DataTables, and regardless of whether any given column uses formatted values. I do have the condition that if any cell in a column uses a formatted value, then all of the cells in that column do, but I do not have the condition that every column uses formatted values. If a column does not use formatted values, then I want the normal sort based on the type of data in the column (e.g., number, string, date, etc.).

As an example, I could have a DataTable like the following. (This data is not actual data, but mirror situations that are problematic for me.)

var dataTable = new google.visualization.DataTable({
    cols: [
        {type: 'number', label: 'ID'},
        {type: 'number', label: 'Places'},
        {type: 'string', label: 'Things'}
    ],
    rows: [
        {c:[{v:1},{v:102735,f:'Harbor Place'},{v:'pet',f:'Dalmation'}]},
        {c:[{v:2},{v:163848,f:'Alphaville'},{v:'my',f:'Top Favorites'}]},
        {c:[{v:3},{v:113787,f:'Beta City'},{v:'ten',f:'Best Things'}]}
    ]
});

Without my doing any special configuration, this table, if sorted ascending on the ID column, would look like this, as the user would expect:

ID   Places          Things
----------------------------------
1    Harbor Place    Dalmation
2    Alphaville      Top Favorites
3    Beta City       Best Things

If the user clicks on the header for the Places column, the table then looks like this, because the ascending sort is performed on the underlying numeric values:

ID   Places          Things
----------------------------------
1    Harbor Place    Dalmation
3    Beta City       Best Things
2    Alphaville      Top Favorites

The user, however, expects the table to look like this:

ID   Places          Things
----------------------------------
2    Alphaville      Top Favorites
3    Beta City       Best Things
1    Harbor Place    Dalmation

If the user clicks on the header for the Things column, the table would sort ascending as follows, because of the sort order of the underlying string values:

ID   Places          Things
----------------------------------
2    Alphaville      Top Favorites
1    Harbor Place    Dalmation
3    Beta City       Best Things

The user is expecting this:

ID   Places          Things
----------------------------------
3    Beta City       Best Things
1    Harbor Place    Dalmation
2    Alphaville      Top Favorites

I've searched on StackOverflow and on Google and haven't found any discussion of this particular situation. So I've been working on writing my own event listener, but it quickly became very involved when trying to write it as something that can be reused for any of my Tables. So am I missing something simple? Has anyone else had a situation like this and resolved it; if so, what did you do?

回答1:

you can use the DataView Class to set the row order

dataView.setRows([1, 2, 0]);

then draw the chart with the DataView

table.draw(dataView, options);

getting the order of the rows during the 'sort' event is the tricky part,
since there aren't any convenience methods for formatted values, such as getDistinctValues

in the following working snippet, the formatted values are extracted into an array,
sorted, then getFilteredRows is used to get the row index of the formatted value

google.charts.load('current', {
  callback: function () {
    var dataTable = new google.visualization.DataTable({
      cols: [
        {type: 'number', label: 'ID'},
        {type: 'number', label: 'Places'},
        {type: 'string', label: 'Things'}
      ],
      rows: [
        {c:[{v:1},{v:102735,f:'Harbor Place'},{v:'pet',f:'Dalmation'}]},
        {c:[{v:2},{v:163848,f:'Alphaville'},{v:'my',f:'Top Favorites'}]},
        {c:[{v:3},{v:113787,f:'Beta City'},{v:'ten',f:'Best Things'}]}
      ]
    });

    // use DataView to set order of rows
    var dataView = new google.visualization.DataView(dataTable);
    dataView.setRows([1, 2, 0]);

    var table = new google.visualization.Table(document.getElementById('chart_div'));
    var options = {
      // use event to set order
      sort: 'event',

      // set column arrow
      sortColumn: 1,
      sortAscending: true
    };

    google.visualization.events.addListener(table, 'sort', function(props) {
      var sortValues = [];
      var sortRows = [];
      var sortDirection = (props.ascending) ? 1 : -1;

      // load values into sortable array
      for (var i = 0; i < dataTable.getNumberOfRows(); i++) {
        sortValues.push({
          v: dataTable.getValue(i, props.column),
          f: dataTable.getFormattedValue(i, props.column)
        });
      }
      sortValues.sort(function (row1, row2) {
        return row1.f.localeCompare(row2.f) * sortDirection;
      });

      // set row indexes
      sortValues.forEach(function (sortValue) {
        sortRows.push(dataTable.getFilteredRows([{column: props.column, value: sortValue.v}])[0]);
      });

      // use DataView to set order of rows
      dataView.setRows(sortRows);

      // set column arrow
      options.sortColumn = props.column;
      options.sortAscending = props.ascending;

      table.draw(dataView, options);
    });

    table.draw(dataView, options);
  },
  packages: ['table']
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>