Created new plugins for more advanced column-specific filters via the Datatables API

kjhangiani

Hi guys,

I've been using datatables in an ember app for a while now, and was looking for a good way to abstractly do column filtering on things like date ranges, numeric ranges, etc. The examples all provided seem to be based around using fixed ID elements for the inputs, and specifying explicitly which columns they operate on on a case by case basis. However, we are using datatables as a more generic component, and I was looking for a solution that would allow me to do filtering via the datatables API, and that would also preserve state in the same way that normal search / column search does. Since I am using datatables in an ember app, I am pretty much exclusively operating on the API, and am not using much of the markup from datatables itself, so the examples, while helpful in terms of logic, were not suitable for what I needed

I couldn't find anything pre-built that accomplished what I wanted, so I have just created 2 plugins that might be useful for others in the same situation.

The first plugin is called datatables-metadata (https://github.com/kjhangiani/datatables-metadata) and can be used to store arbitrary column specific data in the datatable object. This data will persist using the same state saving settings that your table uses, and so can be used to include any arbitrary payload that you might find useful.

The API is listed in full on the github link above, but essentially will allow you to do something like .column('id:name').meta.set(key, value) to store arbitrary data that can be referenced from .column('id:name').meta() or .column('id:name').meta(key)

This plugin was created to facilitate the primary plugin, which is datatables-improved-filters (https://github.com/kjhangiani/datatables-improved-filters) This plugin extends the datatable API to add various useful (imo) column specific filtering options, in addition to the built in 'search' option. It does not come with any markup on its own, but should make it easy to create various types of filters using the API. Currently I have added filters for various numeric and date ranges, but I plan to continue adding filters as needed. These filters utilize the metadata plugin so that they are state saved in the same way that search and column search is.

Here is the full API of both Metadata and Improved Filters below (copied from READMEs)

Metadata

Store column specific metadata in the datatable. This is used by the filtering functions below, but can also be used to store arbitrary data related to the column as required. This data is state saved if state saving is enabled. At the current time, data should always be an object

• .column().meta() // retrieve meta information for a column
• .column().meta(key) // retrieve meta information for a column, under the key key
• .column().meta.replace(data) // set the entire column meta to the object passed in as data
• .column().meta.clear() // remove all metadata for this column
• .column().meta.set(key, data) // set the key property in the column metadata object to data
• .column().meta.merge(key, data) // merge the key property in the column metadata object with the object passed in to data
• .column().meta.remove(key) // remove all metadata under key

Utility

• .pick(attr1, attr2, ... attrN) // similar to .pluck(), but returns a new instance of the Datatables API with the result set being an array of objects with the specified keys. Intended to be used when chained after .data() or .columns().data() or similar api calls.
• .pick([attr1, attr2, ... attrN]) // identical to above, but accepts the attributes in an array. this version will be used if arguments.length===1 and the first argument is an array.

Improved Filters

Filter columns using various utility functions. Column filtering is analagous to searching (and uses search orthogonal data), and is state saved (via the metadata plugin)

Date filters require moment.js

• .column().if() // clear all filters on the specified column
• .column().if.gt(min) // numeric filter for rows that contain data in this column that is greater than (gt) min
• .column().if.gt() // clear gt filter
• .column().if.lt(max) // numeric filter for rows that contain data in this column that is less than (lt) max
• .column().if.lt() // clear lt filter
• .column().if.between(min, max) // numeric filter for data between min and max, equivalent to .if.gt(min).if.lt(max)
• .column().if.between() // clear between filter
• .column().if.after(dateMin) // requires moment.js date filter for rows that contain dates in this column that is after dateMin.
• .column().if.after() // clear after filter
• .column().if.before(dateMax) // requires moment.js date filter for rows that contain dates in this column that is before dateMax.
• .column().if.before() // clear before filter
• .column().if.during(dateMin, dateMax) // requires moment.js date filter for data between dateMin and dateMax, equivalent to .if.after(dateMin).if.before(dateMax)
• .column().if.during() // clear during filter

Any feedback is welcome, feature requests, etc - let me know if this is at all useful for anyone. I also would love feedback on the correct coding style/syntax to use for the plugins themselves - I basically used the Buttons extension as a template, as well as the plugin documentation here on the datatables site.

Both of these are available on bower. Improved Filters requires Metadata. Install via:

bower install datatables-metadata
bower install datatables-improved-filters

kjhangiani

Hmm... I probably should have posted this in the plugins subforum, did not even realize there were subsections until just now! If someone could move this to the right section, that'd be appreciated! :)

dtableuser

@kjhangiani what version were you using? How would you rate the performance of table

allan

@kjhangiani - This is awesome! I really like what you've built here - particularly the pick method - that's superb.

The enhanced filtering is really clever. I'm very keen to improve DataTables filtering abilities going forward, so its a pleasure to see this.

Thanks so much for sharing this with us. (I've moved it to Plugins and added full forum access to your account so you can post there in future).

Regards,
Allan

kjhangiani

@allan thanks for the kind words! I plan to add a few more filtering options soon, and I'll be sure to keep you updated. As far as pick goes, feel free to just add it to the core if you want, I found myself needing it so I just stuck it in these 2 plugins, but it's just a random extra function here, so if it makes more sense to package it up in the core, go for it. It's modeled after lodash/underscore, which uses pick and pluck with the same meanings.

I also am not sure if the plugin is compatible with ColReorder yet, as I have to maintain my own indices...we don't use that plugin currently so I haven't tested it out, but I will try to get that updated as well

I can also probably drop the moment.js dependency pretty easily with Date.parse instead, and will likely do that in the next release.

Are there any other common filter types that people usually request?

One thing that I would really want is a way to extend the built-in orthogonal data types, without having to resort to the 'render' callback. It would be nice to be able to add a first-class type to the cache ('export' is the particular one that I want, but variations of orthogonal data are definitely useful). I am not sure if this can be done via a plugin, or if it'd have to be done through the core.

allan

Are there any other common filter types that people usually request?

Not off the top of my head. Some can use fairly complex logic which could be handled in a custom callback, but I think other methods would be appropriate for that (map for example).

Regarding the Moment dependency - very few people actually want to use ISO8601 date formats for their clients. It might be worth considering have Moment supported for anything else, but without Moment only ISO8601 would work.

export

I've considered this a number of times. The default is to use the display since I think you would generally expect to get what is shown in the table, but it is possible to override that using the button's configuration options to request a different orthogonal data type.

Regards,
Allan