This blog post will take you through creating a custom server-side filtering user interface that does not follow dataTables default column filtering ui. If you've yet to read my previous post please do so before reading this one or you could be confused by some of the code that I do not explain. http://justinmichaels.net/using-jquery-datatables-with-asp-net-mvc-for-serverside-filtering-sorting-and-paging

Custom server-side filtering with jQuery dataTables and ASP.NET Mvc 3

I’m going to start this post by explaining why I felt the need for custom filtering for jQuery dataTables. The issue that I’ve ran in to in the past is all of the built-in ui for filtering is done on each individual column. This is great for tables with a small number of columns but less than ideal for most of my scenarios.

alt text

This is sometimes a tough sell when you have over 10 columns on a form which leaves little width for each column. The following code will walk you through exactly how to create a custom filter that will plug in to our server-side dataTable implementation.

Let’s go ahead and add a new model to the Models folder called CustomerSearchModel. This will give us our filtering information that we can use when we go to grab our slice of data out of the InMemoryCustomersRepository that we created previously.

public class CustomerSearchModel
{
    [DisplayName("Name:")]
    public string Name { get; set; }

    [DisplayName("Birthday Range:")]
    public DateTime? BeginBirthday { get; set; }

    public DateTime? EndBirthday { get; set; }

    [DisplayName("Age Range:")]
    public int? BeginAge { get; set; }

    public int? EndAge { get; set; }

    [DisplayName("Phone Number:")]
    public string PhoneNumber { get; set; }
}

Next we’ll add a view called CustomSearch.cshtml to the Views/Customer folder that will be based off our CustomerSearchModel that we defined. We’ll use our strongly typed helpers to define our inputs for our custom filtering.

@model jQuery.DataTables.Web.Models.CustomerSearchModel
@{
    ViewBag.Title = "Custom Search";
}
<h2>jQuery dataTables custom filtering example</h2>
<div id="SearchContainer" style="border: 3px dashed red; padding: 25px;">
    <table>
        <tr>
            <td>
                @Html.LabelFor(m => m.Name)
            </td>
            <td>
                @Html.TextBoxFor(m => m.Name)
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(m => m.BeginAge)
            </td>
            <td>
                @Html.TextBoxFor(m => m.BeginAge)<span style="margin: 0px 10px;">-</span>@Html.TextBoxFor(m => m.EndAge)
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(m => m.BeginBirthday)
            </td>
            <td>
                @Html.TextBoxFor(m => m.BeginBirthday)<span style="margin: 0px 10px;">-</span>@Html.TextBoxFor(m => m.EndBirthday)
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(m => m.PhoneNumber)
            </td>
            <td>
                @Html.TextBoxFor(m => m.PhoneNumber)
            </td>
        </tr>
    </table>
    <input type="button" id="Search" value="Search" />
    <input type="button" id="ClearSearch" value="Clear Search" style="margin-left: 50px;" />
</div>
<table id="customers">
    <thead>
        <tr>
            <th>
                Last Name
            </th>
            <th>
                First Name
            </th>
            <th>
                Age
            </th>
            <th>
                Birthday
            </th>
            <th>
                Phone Number
            </th>
        </tr>
    </thead>
</table>
<script src="@Url.Content("~/Scripts/DataTables-1.9.1/media/js/jquery.dataTables.js")"></script>

Now we’ll define our GET and POST actions in our CustomerController. If you look at the post action you'll notice I've defined a second parameter of type CustomerSearchModel that will be populated by the DefaultModelBinder when dataTables sends its request. The reason I point this out is the first parameter JQueryDataTablesModel is being bound by the JQueryDataTablesModelBinder that we wrote previously. The reason we had to do this is because of the naming conventions that dataTables uses when sending the request. Our custom filtering javascript plugin will handle sending the name generated by our ASP.NET Mvc 3 strongly typed helpers which will let us use the DefaultModelBinder for model binding.

[HttpGet]
public ViewResult CustomSearch()
{
    return View("CustomSearch");
}

[HttpPost]
public JsonResult CustomSearch(JQueryDataTablesModel jQueryDataTablesModel, CustomerSearchModel searchModel)
{
    int totalRecordCount;
    int searchRecordCount;
    var customers = InMemoryCustomersRepository.GetCustomers(startIndex: jQueryDataTablesModel.iDisplayStart,
        pageSize: jQueryDataTablesModel.iDisplayLength, sortedColumns: jQueryDataTablesModel.GetSortedColumns(),
        totalRecordCount: out totalRecordCount, searchRecordCount: out searchRecordCount, searchModel: searchModel);

    return this.DataTablesJson(items: customers,
        totalRecords: totalRecordCount,
        totalDisplayRecords: searchRecordCount,
        sEcho: jQueryDataTablesModel.sEcho);
}

We next have to write a wrapper for dataTables since the only extension points for filtering are for client-side only. I searched through the documentation and found a nifty callback function called fnServerParams. The documentation for the function states the following:

It is often useful to send extra data to the server when making an Ajax request - for example custom filtering information, and this callback function makes it trivial to send extra information to the server. The passed in parameter is the data set that has been constructed by DataTables, and you can add to this or modify it as you require.

Bingo, this is exactly what I was looking for. Now we have a way to push our filtering inputs name and value to the server without having to touch the dataTables javascript. I decided to just create a new function property for the jQuery.fn object called dataTableWithFilter. This function will essentially take the json that is usually passed to the dataTable function but will require one additional property that will be used for custom filtering. The property is called filterOptions and contains three properties of its own.

  1. searchButton - corresponds to the button that you want to use to start the custom filtering. This should not be a submit button since I am not canceling the click event. After finding all of the inputs and storing their data in the searchCriteria array of json objects.
  2. clearSearchButton - this button is in charge of resetting the searchCriteria array and inputs. This will force dataTables to make a request to the server.
  3. searchContainer - this is the id of the search container for the custom filtering. The searchContainer is an element that encompasses all of the custom filtering inputs. This will let my custom filter plugin know where it should search for the inputs.

Add a new JavaScript file to the Scripts folder called jquery.dataTables.custom-filter.js. Add the following code to the file:

(function ($) {
    jQuery.fn.dataTableWithFilter = function (settings) {
        // alias the original jQuery object passed in since there is a possibility of multiple dataTables and search containers on a single page.
        // If we don't do this then we run the risk of having the wrong jQuery object before forcing a dataTable.fnDraw() call
        var $dataTable = this,
        searchCriteria = [],
        filterOptions = settings.filterOptions,
        // retrieves all inputs that we want to filter by in the searchContainer
        $searchContainerInputs = $('#' + filterOptions.searchContainer).find('input[type="text"],input[type="radio"],input[type="checkbox"],select,textarea');
        // remove the filterOptions object from the object literal (json) that will be passed to dataTables
        delete settings.filterOptions;
        if (filterOptions === undefined) {
            throw {
                name: 'filterOptionsUndefinedError',
                message: 'Please define a filterOptions property in the object literal'
            };
        }
        if (filterOptions.searchButton === undefined) {
            throw {
                name: 'searchButtonUndefinedError',
                message: 'Please define a searchButton in the filterOptions'
            };
        }
        if (filterOptions.clearSearchButton === undefined) {
            throw {
                name: 'clearSearchButtonUndefinedError',
                message: 'Please define a clearSearchButton in the filterOptions'
            };
        }
        if (filterOptions.searchContainer === undefined) {
            throw {
                name: 'searchContainerUndefinedError',
                message: 'Please define a searchContainer in the filterOptions'
            };
        }
        $searchContainerInputs.keypress(function (e) {
            if (e.keyCode === 13) {
                // if an enter key was pressed on one of our inputs then force the searchButton click event to happen
                $("#" + filterOptions.searchButton).click();
            }
        });
        $("#" + filterOptions.searchButton).click(function () {
            searchCriteria = [];
            var searchContainer = $("#" + filterOptions.searchContainer);
            searchContainer.find('input[type="text"][value!=""],input[type="radio"]:checked,input[type="checkbox"]:checked,textarea[value!=""],select[value!=""]').each(function () {
                // all textboxes, radio buttons, checkboxes, textareas, and selects that actually have a value associated with them
                var element = $(this), value = element.val();
                if (typeof value === "string") {
                    searchCriteria.push({ "name": element.attr("name"), "value": value });
                }
                else if (Object.prototype.toString.apply(value) === '[object Array]') {
                    // multi select since it has an array of selected values
                    var i;
                    for (i = 0; i < value.length; i++) {
                        searchCriteria.push({ "name": element.attr("name"), "value": value[i] });
                    }
                }
            });
            // force dataTables to make a server-side request
            $dataTable.fnDraw();
        });
        $("#" + filterOptions.clearSearchButton).click(function () {
            searchCriteria = [];
            $searchContainerInputs.each(function () {
                var $input = $(this),
                tagName = this.tagName.toLowerCase();
                if (tagName === "input") {
                    var type = $input.attr("type").toLowerCase();
                    if (type === "checkbox"
                    || type === "radio") {
                        $input.removeAttr("checked");
                    }
                    else if (type === "text") {
                        $input.val("");
                    }
                }
                else if (tagName === "select") {
                    if ($input.attr("multiple") !== undefined) {
                        $input.val([]);
                    }
                    else {
                        $input.val("");
                    }
                }
                else if (tagName === "textarea") {
                    $input.val("");
                }
            });
            $dataTable.fnDraw();
        });
        settings.fnServerParams = function (aoData) {
            var i;
            for (i = 0; i < searchCriteria.length; i++) {
                // pushing each name/value pair that was found from the searchButton click event in to the aoData array
                // which will be sent to the server in the request
                aoData.push(searchCriteria[i]);
            }
        };
        return $dataTable.dataTable(settings);
    };
} (jQuery));

You’ll notice at line 91 that I’ve hooked in to the settings argument which is just a json object for the dataTableWithFilter function and added a method for the fnServerParams callback before initializing the dataTable with the settings object. This is where I’ll take the searchCriteria array and iterate over the array to push the name/value pairs in to the aoData argument which will then pass them in the ajax request. Another piece of code to note is that you'll be able to define whatever settings for the dataTable that you normally would since all I am doing is pulling the filterOptions json object from the settings in order to hook up custom filtering.

Now we're going to add the initialization code for the dataTableWithFilter plugin to the end of our CustomSearch.cshtml view. You'll notice the filterOptions property which is what the plugin looks for when hooking up the custom filtering.

<script src="@Url.Content("~/Scripts/jquery.dataTables.custom-filter.js")"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $('#customers').dataTableWithFilter({
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": document.URL,
            "sServerMethod": "POST",
            "aoColumns": [
            { "mDataProp": "LastName" },
            { "mDataProp": "FirstName" },
            { "mDataProp": "Age" },
            { "mDataProp": "Birthday" },
            { "mDataProp": "PhoneNumber"}],
            "fnRowCallback": function (nRow, aData, iDisplayIndex, iDisplayIndexFull) {
                var birthday = new Date(parseInt(aData.Birthday.replace("/Date(", "").replace(")/", ""), 10));
                $('td:eq(3)', nRow).html(birthday.getMonth() + 1 + "/" + birthday.getDate() + "/" + birthday.getFullYear());
            },
            // Initialize our custom filtering buttons and the container that the inputs live in
            filterOptions: { searchButton: "Search", clearSearchButton: "ClearSearch", searchContainer: "SearchContainer" }
        });
    });
</script>

We’ve now created a reusable jquery plugin that requires little configuration in order to start using custom filtering for our jQuery dataTables server-side implementations.

The code for this can be found on bitbucket at https://bitbucket.org/justinmichaels/jquery.datatables/downloads.

asp.netmvcfilteringjquery dataTables
Posted by: Justin Michaels
Last revised: 03 Aug, 2012 12:14 PM

Comments

No comments yet. Be the first!

blog comments powered by Disqus