Category filter in combination with sorted data - how to make this work

Category filter in combination with sorted data - how to make this work

EensimEensim Posts: 3Questions: 0Answers: 0

I'm trying to get the following:

  • I want to return/show only a single row per unique value in a specific column (in the example: 1 row per city)
  • I want to filter by some column specific criteria (in the example: minimum age is 40)
  • I want to order the data on a specific column, and I want this ordering to take place BEFORE the 'single-row-per-category'-criteria (in the example: order by salary)

So, in the example I would like to return the lowest salary per city, for people who are at least 40.

http://live.datatables.net/viqupay/2/edit

I know this code is quite hacky, but please also note:

  • I did manage to get this working in version 1.9.4
  • I noticed that using the 'filterhack' variable outside of the datatables object gave a performance penalty

And most notably: if the 'order' variable is changed to [5, 'desc'], I get the result I'm looking forward, but ordered in the wrong way.

I realize I could be going about this in the completely wrong way, I'm not so experienced when it comes to JS programming.
That being said: any help or pointers would be greatly appreciated!

Replies

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    What a brilliant question - thank you for this!

    So the way I would approach this is to break it into two individual filters. The way DataTables operates the custom filters is that each one is applied to all rows before the next filter is applied, itself to all rows, etc.

    So the first filter would be the age check and that's fairly trivial:

    $.fn.dataTable.ext.search.push(
      function( settings, data, dataIndex ) {
        return data[3] < 40 ? false : true;
      }
    );
    

    Then we want to do do the unique filter. That can be done by keeping a cache of the cities we have seen before and using the DataTables draw counter to know if we should reset that filter or not. I've done that like this:

    var _uniqueFilter = [];
    var _lastDraw = null;
    
    $.fn.dataTable.ext.search.push(
      function( settings, data, dataIndex ) {
        // If starting a new order, reset the filter
        if ( _lastDraw !== settings.iDraw ) {
          _uniqueFilter = [];
          _lastDraw = settings.iDraw;
        }
        
        // Check if there is already a matching city
        if ( _uniqueFilter.indexOf( data[2] ) !== -1 ) {
          return false;
        }
    
        _uniqueFilter.push( data[2] );
        return true;
      }
    );
    

    Working example: http://live.datatables.net/sisudix/2/edit

    if the 'order' variable is changed to [5, 'desc'], I get the result I'm looking forward, but ordered in the wrong way.

    This is due to a quirk of how the sorting is applied. DataTables is going through the display rows in reverse... It does this simply because it makes removing elements from the array slightly easier.

    I've never specified in the documentation the ordering of how the filters work (when working with custom filters they've always been individual row filters, no interdependency), so I'm inclined to change this as it makes far more sense to loop through the array forwards.

    Let me look into that and I'll post back.

    Regards,
    Allan

  • EensimEensim Posts: 3Questions: 0Answers: 0

    Hi Allan,

    Thanks for your support! Your code looks a lot cleaner than mine, so that's already a big improvement :)

    Just a quick note (you're probably aware of this, but just to be safe!):
    The working example that you posted does not return the person with the lowest salary per city, who is at least 40.

    It's funny that you mentioned this fact about datatables, because that's exactly what I was thinking 'it looks like the results are ordered in reverse'! (i.e. I was getting the highest, instead of the lowest salaries per city etc
    ;)

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    I've just committed the required change and will ensure that this is documented and added as part of the test suite while I'm updating them for 1.10 (the developer documentation is in progress for 1.10...!).

    So a slight update to the example: http://live.datatables.net/sisudix/3/edit .

    I've also had it pass in the loop index so the draw counter isn't needed any more.

    Filtering is one area in DataTables I really want to improve, so thanks for your feedback on this!

    Regards,
    Allan

  • EensimEensim Posts: 3Questions: 0Answers: 0

    Brilliant, going to try this out tomorrow and report back!

This discussion has been closed.