Clever Geek Handbook

Data filtering guidelines

The way I see it, when creating dynamic charts with filters, every time the user requests the filtered data, I can

  • Run a new MySQL query and use MySQL for filtering.

    SELECT date,
  SUM(IF( `column` = `condition`, 1, 0)) as count
  ...

  • Run a new MySQL query and use a server-side language (PHP in my case) for filtering.

    function getData(condition)  {
  $resultSet = mysqli_query($link, "SELECT date, column ... ");

  $count = 0;
  while ($row = mysqli_fetch_assoc($result_set)) {
      if ($row['column'] == 'condition') {
          $count++;
      }
  }
}

  • First execute one MySQL query, pass all data to the client, and use Javascript and d3 to filter.

I expect the answer is not black and white. For example, if a filter is not being requested, it might not make sense to force the other 95% of users to wait for the corresponding data, and therefore the filter will require a new data call. But I do think about edge cases - situations where filters are used regularly but idiosyncratic. At times like this, is it better to put the filtering logic in the frontend, in the background, or in my database queries?

+3


source to share


1 answer


Generally, if filtering can be done at the front end, it should be done there. Benefits:

  • It doesn't matter if your server goes down.
  • Saves you bandwidth costs.
  • Saves the user waiting round trip time.

The disadvantages are that it can be slower and more complex than the backend. However, depending on the amount of data, there are many cases (like your examples) where Javascript is good enough. d3 has a built-in filter function:



//remove anything that isn't cake
d3.selectAll('whatever')
  .filter(function(d){return d.type != 'cake'})
  .remove()

If you need more complex filtering like basic aggregates, you can use Crossfilter (also from Mike Bostock) or the excellent d3 + dc.js cross filter wrapper .

+5


source






More articles:


All Articles