Saturday, 14 December 2013

Live search filter for jquery handsontable

Search:



I normally don't blog much about my coding skills which is the job that I do daily but since I like Jquery very much I might start doing it more often. Here I have shared a simple live search filter for Handsontable. Just ensure that you declare myData globally in your javascript.

Update:
Since many have asked as to how to keep the changes made to the grid persistent during searching and pagination operations I have added three plugin hooks to model the changes made to the grid to the original data source. You need to hook onto these three events of handsontable.
afterChange - to model the changes when the user searches for a row and edits a cell.
beforeRemoveRow - when the user removes a particular row.
afterCreateRow - when the user inserts a new row at a particular index.
With this your grid is ready and you can post the data to the server to save it in your database. You can do the validations at the client-side or at the server-side.
<style>
.pagination {
        padding: 2px 0;
        margin: 2px;
    }
.pagination a {
            border: 1px solid grey;    
            padding: 2px 5px;
        } 
</style>

<script type="text/javascript">



$(document).ready(function (){

//have a unique row index or a unique running number in your original data source for preserving persistent changes made to the grid. 

var myData = [["Maria Anders", "Sales Representative", "Alfreds Futterkiste", "Germany", "00-800-531-82220", "R||0"],
["Ana Trujillo", "Owner", "Ana Trujillo Emparedados y helados", "Mexico", "00-100-531-7676", "R||1"],
["Antonio Moreno", "Owner", "Antonio Moreno Taquería", "Mexico", "00-200-541-820", "R||2"],
["Thomas Hardy", "Sales Representative", "Around the Horn", "UK", "00-80-591-5620", "R||3"],
["Christina Berglund", "Order Administrator", "Berglunds snabbköp", "Sweden", "00-80-232-86320", "R||4"],
["Hanna Moos", "Sales Representative", "Blauer See Delikatessen", "Germany", "00-300-521-89890", "R||5"],
["Frédérique Citeaux", "Marketing Manager", "Blondel père et fils", "France", "00-200-531-82520", "R||6"],
["Martín Sommer", "Owner", "Bólido Comidas preparadas", "Spain", "00-809-431-82320", "R||7"],
["Laurence Lebihan", "Owner", "Bon app'", "France", "00-800-531-82220", "R||8"],
["Elizabeth Lincoln", "Accounting Manager", "Bottom-Dollar Markets", "Canada", "00-400-561-89220", "R||9"],
["Victoria Ashworth", "Sales Representative", "B's Beverages", "UK", "00-800-531-82220", "R||10"],
["Patricio Simpson", "Sales Agent", "Cactus Comidas para llevar", "Argentina", "00-800-531-82229", "R||11"],
["Francisco Chang", "Marketing Manager", "Centro comercial Moctezuma", "Mexico", "00-800-531-82645", "R||12"],
["Yang Wang", "Owner", "Chop-suey Chinese", "Switzerland", "00-100-521-82456", "R||13"],
["Pedro Afonso", "Sales Associate", "Comércio Mineiro", "Brazil", "00-700-431-85620", "R||14"],
["Elizabeth Brown", "Sales Representative", "Consolidated Holdings", "UK", "00-600-231-82900", "R||15"],
["Sven Ottlieb", "Order Administrator", "Drachenblut Delikatessen", "Germany", "00-200-561-89220", "R||16"],
["Janine Labrune", "Owner", "Du monde entier", "France", "00-300-431-88720", "R||17"],
["Ann Devon", "Sales Agent", "Eastern Connection", "UK", "00-201-531-82850", "R||18"],
["Roland Mendel", "Sales Manager", "Ernst Handel", "Austria", "00-802-541-82820", "R||19"]];


var noOfRowstoShow = 4; //set the maximum number of rows that should be displayed per page.

$("#exampleGrid").handsontable({
    startRows: 5,
    startCols: 5,
    rowHeaders: true,
    colHeaders: true,
    columnSorting: true,
    colHeaders: ["Employee Name", "Designation", "Company Name", "Country", "Contact No"],
    contextMenu: ["row_below", "remove_row", "undo", "redo", "sep1", "sep2", "sep3"],
    columns: [{ data: 0, type: 'text' }, { data: 1, type: 'text' }, { data: 2, type: 'text' }, { data: 3, type: 'text' }, { data: 4, type: 'text'}],
    afterChange: function (change, source) {
        if (source === 'edit') {
            var datarow = $("#exampleGrid").handsontable('getDataAtRow', change[0][0]);
            for (row = 0, r_len = myData.length; row < r_len; row++) {
                for (col = 0, c_len = myData[row].length; col < c_len; col++) {
                    if (myData[row][col] == datarow[5]) {
                        myData[row][change[0][1]] = change[0][3];
                    }
                }
            }
        }
    },
    afterCreateRow: function (index, amount) {
        var rowvalue = myData[myData.length - 1][5];
        var rowno = rowvalue.split("||");
        var newrow = ["", "", "", "", "", "R||" + (parseInt(rowno[1]) + 1)];

        myData.splice(index, 0, newrow);
        getgridData(myData, "1", noOfRowstoShow);
    },
    beforeRemoveRow: function (index, amount) {
        var removerow = $("#exampleGrid").handsontable('getDataAtRow', index);
        var flag = false;
        for (row = 0, r_len = myData.length; row < r_len; row++) {
            for (col = 0, c_len = myData[row].length; col < c_len; col++) {
                if (myData[row][col] == removerow[5]) {

                    myData.splice(row, 1);
                    flag = true;
                }
                if (flag == true) {
                    break;
                }
            }
            if (flag == true) {
                break;
            }
        }

    }
});

loadData();

function loadData() {
    getgridData(myData, "1", noOfRowstoShow);
}

$('#searchgrid').on('keyup', function (event) {
    var value = ('' + this.value).toLowerCase(), row, col, r_len, c_len, td;
    var example = $('#exampleGrid');
    var data = myData;
    var searcharray = [];
    if (value) {
        for (row = 0, r_len = data.length; row < r_len; row++) {
            for (col = 0, c_len = data[row].length; col < c_len; col++) {
                if (data[row][col] == null) {
                    continue;
                }
                if (('' + data[row][col]).toLowerCase().indexOf(value) > -1) {
                    searcharray.push(data[row]);
                    break;
                }
                else {
                }
            }
        }
        getgridData(searcharray, "1", noOfRowstoShow);
    }
    else {
        getgridData(myData, "1", noOfRowstoShow);
    }
});

function getgridData(res, hash, noOfRowstoShow) {

    var page = parseInt(hash.replace('#', ''), 10) || 1, limit = noOfRowstoShow, row = (page - 1) * limit, count = page * limit, part = [];

    for (; row < count; row++) {
        if (res[row] != null) {
            part.push(res[row]);
        }
    }

    var pages = Math.ceil(res.length / noOfRowstoShow);
    $('#gridpage').empty();
    for (var i = 1; i <= pages; i++) {
        var element = $("<a href='#" + i + "'>" + i + "</a>");
        element.bind('click', function (e) {
            var hash = e.currentTarget.attributes[0].nodeValue;
            $('#exampleGrid').handsontable('loadData', getgridData(res, hash, noOfRowstoShow));
        });
        $('#gridpage').append(element);
    }
    $('#exampleGrid').handsontable('loadData', part);
    return part;
}
});
</script>

Search: <input id="searchgrid" type="text" />

<div id="exampleGrid">
</div>
<div id="gridpage" class="pagination">
  </div>

31 comments :

  1. Thats really great....one thing to note: if you delete any record from the search data, it doesnt reflect when the search is cleared out.

    ReplyDelete
    Replies
    1. Hello Aady,

      "Handsontable binds to your data source (array or object) by reference. Therefore, all the data entered in the grid will alter the original data source."

      Now delete a record and search to see your changes being reflected and persisted, it was not reflecting before properly because there was a mistake in the way I was loading the scripts.

      Delete
    2. Hello Harshit,

      Thank you for your reply.
      You are right it works that way but not the other way around. So If I search for 2008 and then delete that row from search result and then clear 2008 from search textbox then 2008 appears back in the list. Basically I want to allow search and delete functionality within main grid.

      Delete
    3. Hello Aady,

      I got what you mean and I have solved that problem in my project by sending a unique row index for my reference with the array.

      var myData = [
      ["", "Kia", "Nissan", "Toyota", "Honda", "R||0"],
      ["2008", 10, 11, 12, 13, "R||1"],
      ["2009", 20, 11, 14, 13, "R||2"],
      ["2010", 30, 15, 12, 13, "R||3"]
      ];

      Use the column options of Handsontable to display only those data which you want to display in the grid and the rest of the data will be hidden and then I use the beforeRemoveRow event to splice the deleted row from the global data source based on the unique row index which I had sent for my reference. This is how I model the changes made to the grid to my global data source.

      beforeRemoveRow: function (index, amount) {
      var removerow = $("#exampleGrid").handsontable('getDataAtRow', index);
      var flag = false;
      for (row = 0, r_len = myData.length; row < r_len; row++) {
      for (col = 0, c_len = myData[row].length; col < c_len; col++) {
      if (myData[row][col] == removerow[5]) {

      myData.splice(row, 1);
      flag = true;
      }
      if (flag == true) {
      break;
      }
      }
      if (flag == true) {
      break;
      }
      }

      }

      In fact I have implemented Live search, pagination, delete row functionality, typeahead autocomplete altogether within the main grid with this approach. I hope you got my solution to the problem.

      Delete
    4. Thanks Harshit!

      I will try this out and let you know how it goes. Thanks for your help.

      Delete
    5. Hello Harshit,

      My requirements have changed a bit and want to have a next and previous button that will show the row for the searched term. Is it possible with Handsontable?

      Delete
    6. I guess you mean you want pagination along with search, I have updated the demo and the code for you, I hope this is what you want.

      Delete
    7. this lign doesn't work for me
      var element = $("" + i + "");
      I replaced by this syntax
      var element = $('' + i + '');
      Now it doesn't return error, but element there is nothing inside. Please help

      Delete
    8. I think you want this.

      var element = $(" ' " + i + " ' ");

      Not sure what you are up to. Please prepare a jsfiddle of what you are trying to achieve.

      Delete
    9. Jihen Arem: Oops! the problem was while pasting html code I did not converted the escaped characters.

      I have updated the correct code. Hope it helps you.

      Delete
  2. Hey, this is a really great! But I wanted to ask, how do I can clearly remove pagination without influence on searching.

    ReplyDelete
    Replies
    1. I have prepared a js fiddle without the pagination.

      http://jsfiddle.net/hU6Kz/2892/

      Delete
  3. Is there anyway to do an advanced search on this. For Example. I want to query the records for 'Designation' = 'Owner' and 'Country' = 'Mexico'.
    Please Help! Thanks

    ReplyDelete
    Replies
    1. I have created a jsfiddle for you with 2 search fields.

      http://jsfiddle.net/bhaumikmehta/x6Lp4f4n/

      Delete
    2. if you want with one search field and another dropdown then here is the fiddle link: http://jsfiddle.net/bhaumikmehta/x6Lp4f4n/1/

      Delete
  4. Hey Harshit,

    Great Work man. Hats off to you. I need a help. I need to same functionality, without Pagination offcourse but with key value pair instead of just values.

    Ex:

    var data = [
    {name:"Nissan", year:2009, color1:"black", color2:"black"},
    {name:"Nissan", year:2006, color1:"blue", color2:"blue"},
    {name:"Chrysler", year:2004, color1:"yellow", color2:"black"},
    {name:"Volvo", year:2012, color1:"white", color2:"gray"}
    ];

    I tried a lot to tweak your code but can't. Can you please help me?

    ReplyDelete
    Replies
    1. In this example I have used array of arrays as the data source but yours is an array of objects data source. You can make it work but you have to change my code. Use $.each for iterating over your array of objects rather than 'for' loop. Make a javascript object constructor so that you can make new instances of your object and push it into a array on the fly and then load it into the grid as usual. It should work.

      function carmodel(name, year, color1, color2)
      {
      this.name = name;
      this.year = year;
      this.color1 = color1;
      this.color2 = color2;
      }

      So the search function would change into something like this.

      var searcharray = [];

      if(value){
      $.each(data, function(i,obj) {
      $.each(obj, function(key, val) {
      if (('' + val.toLowerCase().indexOf(value) > -1) {
      searcharray.push(new carmodel(obj.name, obj.year, obj.color1, obj.color2));
      return false;
      }
      });
      });

      getgriddata("...") //no changes here
      }
      else{
      getgriddata("...") //no changes here
      }

      I have no time to prepare a jsfiddle but this should work. All the best!

      Delete
    2. Thanks Harshit. But my main problem is I cant's use key names as coulumns are dynamic. no fixed number of columns are there.

      I can't use "this.name" or "this.year" etc. as there will be "n" number of columns. it could be 5 or 500.

      Please help

      Delete
    3. Then don't use a constructor just push obj into the searcharray.

      $.each(data, function(i,obj) {
      $.each(obj, function(key, val) {
      if (('' + val.toLowerCase().indexOf(value) > -1) {
      searcharray.push(obj);
      return false;
      }
      });
      });

      Then load the searcharray to the grid as usual it will work.

      Delete
    4. Don't forget to remove column options and column headers since your columns are dynamic I don't think you should be using it. Just load the data to the grid. If you want to display only specific set of columns then I guess you would need this.

      Delete
    5. Thanks Harshit. I really appreciate your help. God bless.

      Delete
  5. Hi H. from Italy, I'm trying to use your demo to filter data with handsontable 0.18 (https://github.com/handsontable/handsontable/releases/tag/0.18.0). I have the error Object doesn't support property or method 'handsontable' on line $("#exampleGrid").handsontable({...... What is my problem ? do tou have a costom libreries ? I use jquery-1.10.2.min.js. thanks a lot

    ReplyDelete
    Replies
    1. Your version is right. Ensure that you load this file properly in your code.

      http://handsontable.com/bower_components/handsontable/dist/handsontable.full.js

      Delete
    2. You can refer this jsfiddle if you are still facing any problems.

      http://jsfiddle.net/hU6Kz/2892/

      Delete
  6. This is awesome. Thanks so very much

    ReplyDelete
  7. Hi Harshit, this code is great, but I have a problem. I fetch the data from a sql query (json) and my filter is with a "select" (combo box). When I filter and change any cell value, the data changes in the cell I chose and in the cell of the position (I don't know how to explain it well).

    ReplyDelete
  8. Hi harshit, this code is great, but I have a problem. I fetch the data from a json using ajan and mysql. I use the filter with a select (or combobox), but when I filter and change any cell value, the changes also appears duplicated (For example, I filter, change the first row, ann when I return to the general list, the change is on the first row and where I made the change). I don't know how to explain it well. I hope you understand.

    ReplyDelete
    Replies
    1. Hi Federico Galvan,

      Please check line number #57 in my code. For some strange reason that condition is being satisfied twice and therefore your cell edits are duplicated.

      Please ensure that you have correctly generated the unique row index from the server side like this "R||0", "R||1", "R||2", "R||3"......... (See my reply to Aady's comment in the beginning for the explanation.)

      Depending on the column position where you are generating this unique row index in your array of arrays from the server side you need to change the datarow column number accordingly. I am generating the unique row index at the 5th column or at the last index of the array and hence for me it will be `datarow[5]` but for you it might be different.

      if (myData[row][col] == datarow[5]) <===== (Check this line (57), debug using firebug)
      {
      myData[row][change[0][1]] = change[0][3];
      }

      Delete
  9. how do i use this? As in, where do I place this code?

    ReplyDelete
    Replies
    1. http://docs.handsontable.com/0.19.0/tutorial-quick-start.html

      Delete