Saturday, July 14, 2012

How to build a grid with knockout - Part 3: Sorting and Styling

In Part 3 of this tutorial we're going to add the ability to sort the data in the grid by a particular column in ascending or descending order. The sorting is going to take place on the server and is going to work in conjunction with the paging that we implemented in Part 2. I've have also added some styling to the grid so the final result will look as follows:



Step 1 - The Javascript
Within our javascript view model I've added a method SetOrder() that can will be called when the user clicks on a column header, this looks as follows:

    function SetOrder(data, event, colName) {
        if (_this.colName === colName) {
            _this.switchSortOrder();
        } else {
            _this.colName = colName;
            _this.sortOrder = "asc";
        }
        setTHClass(event);
        loadDataFromServer();
    } 
 
     _this.SetOrder = SetOrder; 
 


The first thing the code does is check if we're already sorting by the column that has been clicked, if so we reverse the sort order. If this is the first time is has been clicked we set the sort order to ascending. This is a good example of how we can use the member properties of the view model, _this.colName and _this.sortOrder, to remember the state of the grid in between actions.

Once we've ascertained the column we're sorting by and the direction we can call the setTHClass() function to show the correct icon for the sorting column. The code for this looks as follows:

   function setTHClass(event) {
        //remove the existing classes
        $(event.currentTarget).parent().find(".sort_wrapper").children("span").removeClass("asc");
        $(event.currentTarget).parent().find(".sort_wrapper").children("span").removeClass("desc");
        if (_this.sortOrder === "asc") {
            $(event.currentTarget).children(".sort_wrapper").children("span").addClass("asc");
        } else {
            $(event.currentTarget).children(".sort_wrapper").children("span").addClass("desc");
        }
    }
 

 
We make use of css classes asc and desc to show the correct icon on the sorting column. The code first removes all traces of these classes from the columns and then adds the correct one where required. Extensive use of JQuery DOM traversal functions are used to make this task easier.

The other major change is in the loadDataFromServer() function where we now have to pass information to the server specifying how the data is to be sorted, this is done by adding the querystring parameters sidx and sord to the url that we post to the server:

function loadDataFromServer() {
            var url = '/people/data';
            //add paging params
            url += '?rows=' + _this.rowsPerPage() +
            '&page=' + _this.page() +
            //add sorting params     
                           '&sidx=' + _this.colName +          
                           '&sord=' + _this.sortOrder;
 
             $.post(
                url,
                function (data) {
                    _this.records(data.TotalRowsCount);
                    _this.totalPages(data.TotalPageCount);
 
                    var results = ko.observableArray();
                    _this.people.removeAll();
 
                    ko.mapping.fromJS(data.GridData, {}, results);
                    for (var i = 0; i < results().length; i++) {
                        _this.people.push(results()[i]);
                    };
                },
                'json'
            )
        }

Step 2 - The Controller
Now we need to modify the controller to make use of the additional querystring parameters that are being passed through. The first task is to sort our data by the requested column, this can be done by using a set of dynamic linq helpers that Scott Gu blogged about, it's all contained in one file that you will need to download and add to your project. You will also need to add a using clause of System.Linq.Dynamic to your controller class.

Once you have done that we can change our controller code to look as follows:

   public JsonResult Data(int rows, int page, string sidx, string sord)
        {
            IQueryable<Person> qryPeople = GetPersonQuery();

            GridDataResult gridDataResult = new GridDataResult();
            gridDataResult.TotalRowCount = qryPeople.Count();
 
            int totalPages = 1;
            if (rows > 0)
            {
                totalPages = gridDataResult.TotalRowCount / rows;
                if (gridDataResult.TotalRowCount % rows != 0)
                    totalPages += 1;
            }
 
            gridDataResult.TotalPageCount = totalPages;
 
            //now do the sorting
            qryPeople = qryPeople.OrderBy(sidx + " " + sord);
 
            gridDataResult.GridData = qryPeople
                .Skip((page - 1) * rows)
                .Take(rows);
 
            return Json(gridDataResult);
        }

Because we are now using the Dynamic Linq helpers we can order qryPeople using strings, perfect for doing sorting!

Step 3 - The Html
 The final step is modifying the html to make sure that when a column header is clicked the view model is notified to that we can reorder the grid. We do this by binding to the table header using knockout as follows:

<thead>
<tr>
    <th data-bind='click: function(data, event) { SetOrder(data, event, "FirstName")}'>
        <div class="sort_wrapper">First Name<span class="grid_order ui_icon"></span></div>
    </th>
    <th data-bind='click: function(data, event) { SetOrder(data, event, "LastName")}'>
        <div class="sort_wrapper">Last Name<span class="grid_order ui_icon"></span></div>
    </th>
    <th data-bind='click: function(data, event) { SetOrder(data, event, "Age")}'>
        <div class="sort_wrapper">Age<span class="grid_order ui_icon"></span></div>
    </th>
</tr>
</thead> 
 
We now have a grid that supports paging, sorting and has been nicely styled. If you would like to download the code you can do so here.

9 comments:

  1. I dont understand how to download this. At bit bucket it requires me to copy and paste every line of code and build the files manually. Not worth it.

    ReplyDelete
  2. Duh I figured it out at bit bucket you have to click on "getsource". Sorry. Any shot you might add filtering in the future? :)

    ReplyDelete
    Replies
    1. How would you like filtering to work? Some grids have a 'filter' row where you can enter a value for a column and get a set of filtered results. I personally normally want to filter across a couple of columns, e.g. put in a search string and have it check against the firstname, lastname and address for a person so I have a search box outside of the grid. Let me know your use case and I'll see what I can put together - that's the joy of a custom grid you can get it to do exactly what you want!

      Delete

  3. Hey, nice site you have here! Keep up the excellent work!

    ASC Coding

    ReplyDelete
  4. Hi david,

    This is the excellent piece of work. I was looking for the same solution since a week but didnt find any convincing solution..untill I saw your post. I will have to further dig into how to work out filtering underneath each header column...but your example is a very good starting point for me.

    Thanks.
    PH

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Hi David,

    I've been using jqGrid for a few projects now and am still not sure about the styling issue you mentioned. The entire UI is CSS based and there are standard/jQueryUI themes that could be used. The jqGrid samples (specifically for queries posted on Stackoverflow) are pretty comprehensive. I would love to seek a more efficient grid management plugin than jqGrid, if one's available. Could you elaborate further on your reason for using Knockout as a replacement for jqGrid.

    Thanks,
    Steve

    ReplyDelete
  7. Hi Steve,

    I went down the path of using jQuery UI components but found the framework difficult to customize using CSS. I'm no CSS guru but I have a fairly good 'developers' grasp of it.

    I've sinced moved to using Twitter Bootstrap for our CSS and found it to be fantastic by comparison! I would highly recommend rolling your own grid using knockout and Twitter Bootstrap, I plan on adding a blog showing what this might look like.

    In addition, while out the box grids get you up and running quickly I believe that rolling your own using knockout will save you a ton of time further down the track when you need to customise it to meet your business requirements.

    Hope that helps,
    David

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete