Datatables Basics

Introduction

I tend to avoid using libraries like ActiveAdmin for dashboard part of the web app. I like to use Datatables. It is easy to setup. It just needs a JSON response from the server. It includes sorting by column, search and pagination, perfect for admin dashboard. In this article, I will walk you through simple examples to get you upto speed.

Simple Datatables Example

Let's look at a simple datatables example that hard codes the table data.

<html>
<head>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
</head>
<body>

    <table id="example" class="display" style="width:100%">
      <thead>
          <tr>
              <th>Name</th>
              <th>Position</th>
              <th>Office</th>
              <th>Age</th>
              <th>Start date</th>
              <th>Salary</th>
          </tr>
      </thead>
      <tbody>
          <tr>
              <td>Tiger Nixon</td>
              <td>System Architect</td>
              <td>Edinburgh</td>
              <td>61</td>
              <td>2011/04/25</td>
              <td>$320,800</td>
          </tr>
          <tr>
              <td>Garrett Winters</td>
              <td>Accountant</td>
              <td>Tokyo</td>
              <td>63</td>
              <td>2011/07/25</td>
              <td>$170,750</td>
          </tr>
          <tr>
              <td>Ashton Cox</td>
              <td>Junior Technical Author</td>
              <td>San Francisco</td>
              <td>66</td>
              <td>2009/01/12</td>
              <td>$86,000</td>
          </tr>
          <tr>
              <td>Cedric Kelly</td>
              <td>Senior Javascript Developer</td>
              <td>Edinburgh</td>
              <td>22</td>
              <td>2012/03/29</td>
              <td>$433,060</td>
          </tr>
          <tr>
              <td>Airi Satou</td>
              <td>Accountant</td>
              <td>Tokyo</td>
              <td>33</td>
              <td>2008/11/28</td>
              <td>$162,700</td>
          </tr>
          <tr>
              <td>Brielle Williamson</td>
              <td>Integration Specialist</td>
              <td>New York</td>
              <td>61</td>
              <td>2012/12/02</td>
              <td>$372,000</td>
          </tr>
          <tr>
              <td>Herrod Chandler</td>
              <td>Sales Assistant</td>
              <td>San Francisco</td>
              <td>59</td>
              <td>2012/08/06</td>
              <td>$137,500</td>
          </tr>
          <tr>
              <td>Rhona Davidson</td>
              <td>Integration Specialist</td>
              <td>Tokyo</td>
              <td>55</td>
              <td>2010/10/14</td>
              <td>$327,900</td>
          </tr>
          <tr>
              <td>Colleen Hurst</td>
              <td>Javascript Developer</td>
              <td>San Francisco</td>
              <td>39</td>
              <td>2009/09/15</td>
              <td>$205,500</td>
          </tr>
          <tr>
              <td>Sonya Frost</td>
              <td>Software Engineer</td>
              <td>Edinburgh</td>
              <td>23</td>
              <td>2008/12/13</td>
              <td>$103,600</td>
          </tr>
          <tr>
              <td>Jena Gaines</td>
              <td>Office Manager</td>
              <td>London</td>
              <td>30</td>
              <td>2008/12/19</td>
              <td>$90,560</td>
          </tr>
          <tr>
              <td>Quinn Flynn</td>
              <td>Support Lead</td>
              <td>Edinburgh</td>
              <td>22</td>
              <td>2013/03/03</td>
              <td>$342,000</td>
          </tr>
          <tr>
              <td>Charde Marshall</td>
              <td>Regional Director</td>
              <td>San Francisco</td>
              <td>36</td>
              <td>2008/10/16</td>
              <td>$470,600</td>
          </tr>
          <tr>
              <td>Haley Kennedy</td>
              <td>Senior Marketing Designer</td>
              <td>London</td>
              <td>43</td>
              <td>2012/12/18</td>
              <td>$313,500</td>
          </tr>
          <tr>
              <td>Tatyana Fitzpatrick</td>
              <td>Regional Director</td>
              <td>London</td>
              <td>19</td>
              <td>2010/03/17</td>
              <td>$385,750</td>
          </tr>
          <tr>
              <td>Michael Silva</td>
              <td>Marketing Designer</td>
              <td>London</td>
              <td>66</td>
              <td>2012/11/27</td>
              <td>$198,500</td>
          </tr>
          <tr>
              <td>Paul Byrd</td>
              <td>Chief Financial Officer (CFO)</td>
              <td>New York</td>
              <td>64</td>
              <td>2010/06/09</td>
              <td>$725,000</td>
          </tr>
          <tr>
              <td>Gloria Little</td>
              <td>Systems Administrator</td>
              <td>New York</td>
              <td>59</td>
              <td>2009/04/10</td>
              <td>$237,500</td>
          </tr>
          <tr>
              <td>Bradley Greer</td>
              <td>Software Engineer</td>
              <td>London</td>
              <td>41</td>
              <td>2012/10/13</td>
              <td>$132,000</td>
          </tr>
          <tr>
              <td>Dai Rios</td>
              <td>Personnel Lead</td>
              <td>Edinburgh</td>
              <td>35</td>
              <td>2012/09/26</td>
              <td>$217,500</td>
          </tr>
          <tr>
              <td>Jenette Caldwell</td>
              <td>Development Lead</td>
              <td>New York</td>
              <td>30</td>
              <td>2011/09/03</td>
              <td>$345,000</td>
          </tr>
          <tr>
              <td>Yuri Berry</td>
              <td>Chief Marketing Officer (CMO)</td>
              <td>New York</td>
              <td>40</td>
              <td>2009/06/25</td>
              <td>$675,000</td>
          </tr>
          <tr>
              <td>Caesar Vance</td>
              <td>Pre-Sales Support</td>
              <td>New York</td>
              <td>21</td>
              <td>2011/12/12</td>
              <td>$106,450</td>
          </tr>
          <tr>
              <td>Doris Wilder</td>
              <td>Sales Assistant</td>
              <td>Sidney</td>
              <td>23</td>
              <td>2010/09/20</td>
              <td>$85,600</td>
          </tr>
          <tr>
              <td>Angelica Ramos</td>
              <td>Chief Executive Officer (CEO)</td>
              <td>London</td>
              <td>47</td>
              <td>2009/10/09</td>
              <td>$1,200,000</td>
          </tr>
          <tr>
              <td>Gavin Joyce</td>
              <td>Developer</td>
              <td>Edinburgh</td>
              <td>42</td>
              <td>2010/12/22</td>
              <td>$92,575</td>
          </tr>
          <tr>
              <td>Jennifer Chang</td>
              <td>Regional Director</td>
              <td>Singapore</td>
              <td>28</td>
              <td>2010/11/14</td>
              <td>$357,650</td>
          </tr>
          <tr>
              <td>Brenden Wagner</td>
              <td>Software Engineer</td>
              <td>San Francisco</td>
              <td>28</td>
              <td>2011/06/07</td>
              <td>$206,850</td>
          </tr>
          <tr>
              <td>Fiona Green</td>
              <td>Chief Operating Officer (COO)</td>
              <td>San Francisco</td>
              <td>48</td>
              <td>2010/03/11</td>
              <td>$850,000</td>
          </tr>
          <tr>
              <td>Shou Itou</td>
              <td>Regional Marketing</td>
              <td>Tokyo</td>
              <td>20</td>
              <td>2011/08/14</td>
              <td>$163,000</td>
          </tr>
          <tr>
              <td>Michelle House</td>
              <td>Integration Specialist</td>
              <td>Sidney</td>
              <td>37</td>
              <td>2011/06/02</td>
              <td>$95,400</td>
          </tr>
          <tr>
              <td>Suki Burks</td>
              <td>Developer</td>
              <td>London</td>
              <td>53</td>
              <td>2009/10/22</td>
              <td>$114,500</td>
          </tr>
          <tr>
              <td>Prescott Bartlett</td>
              <td>Technical Author</td>
              <td>London</td>
              <td>27</td>
              <td>2011/05/07</td>
              <td>$145,000</td>
          </tr>
          <tr>
              <td>Gavin Cortez</td>
              <td>Team Leader</td>
              <td>San Francisco</td>
              <td>22</td>
              <td>2008/10/26</td>
              <td>$235,500</td>
          </tr>
          <tr>
              <td>Martena Mccray</td>
              <td>Post-Sales support</td>
              <td>Edinburgh</td>
              <td>46</td>
              <td>2011/03/09</td>
              <td>$324,050</td>
          </tr>
          <tr>
              <td>Unity Butler</td>
              <td>Marketing Designer</td>
              <td>San Francisco</td>
              <td>47</td>
              <td>2009/12/09</td>
              <td>$85,675</td>
          </tr>
          <tr>
              <td>Howard Hatfield</td>
              <td>Office Manager</td>
              <td>San Francisco</td>
              <td>51</td>
              <td>2008/12/16</td>
              <td>$164,500</td>
          </tr>
          <tr>
              <td>Hope Fuentes</td>
              <td>Secretary</td>
              <td>San Francisco</td>
              <td>41</td>
              <td>2010/02/12</td>
              <td>$109,850</td>
          </tr>
          <tr>
              <td>Vivian Harrell</td>
              <td>Financial Controller</td>
              <td>San Francisco</td>
              <td>62</td>
              <td>2009/02/14</td>
              <td>$452,500</td>
          </tr>
          <tr>
              <td>Timothy Mooney</td>
              <td>Office Manager</td>
              <td>London</td>
              <td>37</td>
              <td>2008/12/11</td>
              <td>$136,200</td>
          </tr>
          <tr>
              <td>Jackson Bradshaw</td>
              <td>Director</td>
              <td>New York</td>
              <td>65</td>
              <td>2008/09/26</td>
              <td>$645,750</td>
          </tr>
          <tr>
              <td>Olivia Liang</td>
              <td>Support Engineer</td>
              <td>Singapore</td>
              <td>64</td>
              <td>2011/02/03</td>
              <td>$234,500</td>
          </tr>
          <tr>
              <td>Bruno Nash</td>
              <td>Software Engineer</td>
              <td>London</td>
              <td>38</td>
              <td>2011/05/03</td>
              <td>$163,500</td>
          </tr>
          <tr>
              <td>Sakura Yamamoto</td>
              <td>Support Engineer</td>
              <td>Tokyo</td>
              <td>37</td>
              <td>2009/08/19</td>
              <td>$139,575</td>
          </tr>
          <tr>
              <td>Thor Walton</td>
              <td>Developer</td>
              <td>New York</td>
              <td>61</td>
              <td>2013/08/11</td>
              <td>$98,540</td>
          </tr>
          <tr>
              <td>Finn Camacho</td>
              <td>Support Engineer</td>
              <td>San Francisco</td>
              <td>47</td>
              <td>2009/07/07</td>
              <td>$87,500</td>
          </tr>
          <tr>
              <td>Serge Baldwin</td>
              <td>Data Coordinator</td>
              <td>Singapore</td>
              <td>64</td>
              <td>2012/04/09</td>
              <td>$138,575</td>
          </tr>
          <tr>
              <td>Zenaida Frank</td>
              <td>Software Engineer</td>
              <td>New York</td>
              <td>63</td>
              <td>2010/01/04</td>
              <td>$125,250</td>
          </tr>
          <tr>
              <td>Zorita Serrano</td>
              <td>Software Engineer</td>
              <td>San Francisco</td>
              <td>56</td>
              <td>2012/06/01</td>
              <td>$115,000</td>
          </tr>
          <tr>
              <td>Jennifer Acosta</td>
              <td>Junior Javascript Developer</td>
              <td>Edinburgh</td>
              <td>43</td>
              <td>2013/02/01</td>
              <td>$75,650</td>
          </tr>
          <tr>
              <td>Cara Stevens</td>
              <td>Sales Assistant</td>
              <td>New York</td>
              <td>46</td>
              <td>2011/12/06</td>
              <td>$145,600</td>
          </tr>
          <tr>
              <td>Hermione Butler</td>
              <td>Regional Director</td>
              <td>London</td>
              <td>47</td>
              <td>2011/03/21</td>
              <td>$356,250</td>
          </tr>
          <tr>
              <td>Lael Greer</td>
              <td>Systems Administrator</td>
              <td>London</td>
              <td>21</td>
              <td>2009/02/27</td>
              <td>$103,500</td>
          </tr>
          <tr>
              <td>Jonas Alexander</td>
              <td>Developer</td>
              <td>San Francisco</td>
              <td>30</td>
              <td>2010/07/14</td>
              <td>$86,500</td>
          </tr>
          <tr>
              <td>Shad Decker</td>
              <td>Regional Director</td>
              <td>Edinburgh</td>
              <td>51</td>
              <td>2008/11/13</td>
              <td>$183,000</td>
          </tr>
          <tr>
              <td>Michael Bruce</td>
              <td>Javascript Developer</td>
              <td>Singapore</td>
              <td>29</td>
              <td>2011/06/27</td>
              <td>$183,000</td>
          </tr>
          <tr>
              <td>Donna Snider</td>
              <td>Customer Support</td>
              <td>New York</td>
              <td>27</td>
              <td>2011/01/25</td>
              <td>$112,000</td>
          </tr>
      </tbody>
      <tfoot>
          <tr>
              <th>Name</th>
              <th>Position</th>
              <th>Office</th>
              <th>Age</th>
              <th>Start date</th>
              <th>Salary</th>
          </tr>
      </tfoot>
  </table>

<script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.3.1.js"></script>
  <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
  <script>
  $(function(){
    $("#example").dataTable();
  })
  </script>
</body>
</html>

We attach datatables to the table with id example. This one line of code provides, pagination, search and sorting data in columns. It uses jQuery 3.3.1 and datatables 1.10.19.

Providing Data in Javascript

Instead of hard-coding the data in the html, we can use data option of the datatables to provide data in an array containing arrays. The default header of the first column has been changed from: Name to Full Name by using the title option in the columnDefs. The date has been changed to human friendly format, instead of the yyyy/mm/dd format. The table body is empty

<html>
<head>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
</head>
<body>

  <table id="example" class="display" style="width:100%">
    <thead>
        <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
        </tr>
    </thead>

        <tbody>
        </tbody>

    <tfoot>
        <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
        </tr>
    </tfoot>
  </table>

<script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.3.1.js"></script>
  <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
  <script>
  $(function(){
    $("#example").dataTable({
         "data":[        
             ["Tiger Nixon","System Architect","Edinburgh","61", "2011/04/25", "$320,800"],
                 ["Bradley Greer","Software Engineer","London","41","2012/10/13", "$132,000"],              
                 ["Dai Rios", "Personnel Lead", "Edinburgh", "35", "2012/09/26", "$217,500"],
                 ["Jenette Caldwell", "Development Lead", "New York", "30", "2011/09/03", "$345,000"],
                 ["Yuri Berry", "Chief Marketing Officer (CMO)","New York", "40", "2009/06/25", "$675,000"],
                 ["Caesar Vance", "Pre-Sales Support", "New York", "21", "2011/12/12", "$106,450"],
                 ["Doris Wilder", "Sales Assistant","Sidney","23","2010/09/20","$85,600"],
                 ["Angelica Ramos", "Chief Executive Officer (CEO)", "London","47","2009/10/09","$1,200,000"],
                 ["Gavin Joyce","Developer","Edinburgh","42","2010/12/22","$92,575"],
                 ["Jennifer Chang","Regional Director","Singapore","28","2010/11/14","$357,650"],
                 ["Brenden Wagner","Software Engineer","San Francisco","28","2011/06/07","$206,850"],
                 ["Fiona Green","Chief Operating Officer (COO)","San Francisco","48","2010/03/11","$850,000"],
                 ["Shou Itou","Regional Marketing","Tokyo","20","2011/08/14","$163,000"],
                 ["Michelle House","Integration Specialist","Sidney","37","2011/06/02","$ 95,400"],
                 ["Suki Burks","Developer","London","53","2009/10/22","$114,500"],
                 ["Prescott Bartlett","Technical Author","London","27","2011/05/07","$145,000"],
                 ["Gavin Cortez","Team Leader","San Francisco","22","2008/10/26","$235,500"],                
             ["Gloria Little","Systems Administrator","New York","59", "2009/04/10", "$237,500"],
             ["Paul Byrd","Chief Financial Officer (CFO)","New York","64", "2010/06/09", "$725,000"],
             ["Michael Silva","Marketing Designer","London","66", "2012/11/27", "$198,500"],                                                                                                 
             ["Tatyana Fitzpatrick","Regional Director","London","19", "2010/03/17", "$385,750"],
                 ["Haley Kennedy","Senior Marketing Designer","London","43", "2012/12/18", "$313,500"],
             ["Charde Marshall","Regional Director","San Francisco","36", "2008/10/16", "$470,600"],
             ["Quinn Flynn","Support Lead","Edinburgh","22", "2013/03/03", "$342,000"],
             ["Jena Gaines","Office Manager","London","30", "2008/12/19", "$90,560"],
             ["Sonya Frost","Software Engineer","Edinburgh","23", "2008/12/13", "$103,600"],
             ["Colleen Hurst","Javascript Developer","San Francisco","39", "2009/09/15", "$205,500"],                                                                                                
             ["Rhona Davidson","Integration Specialist","Tokyo","55", "2010/10/14", "$327,900"],
                 ["Herrod Chandler","Sales Assistant","San Francisco","59", "2012/08/06", "$137,500"],
             ["Brielle Williamson","Integration Specialist","New York","61", "2012/12/02", "$372,000"],
             ["Airi Satou","Accountant","Tokyo","33", "2008/11/28", "$162,700"],
             ["Cedric Kelly","Senior Javascript Developer", "Edinburgh", "22","2012/03/29", "$433,060"],
             ["Ashton Cox","Junior Technical Author","San Francisco","66", "2009/01/12", "$86,000"],
             ["Garrett Winters","Accountant","Tokyo","63", "2011/07/25", "$170,750"],
                 ["Martena Mccray","Post-Sales support","Edinburgh","46","2011/03/09","$324,050"],
                 ["Unity Butler","Marketing Designer","San Francisco","47","2009/12/09","$85,675"],
                 ["Howard Hatfield","Office Manager","San Francisco","51","2008/12/16","$164,500"],
                 ["Hope Fuentes","Secretary","San Francisco","41","2010/02/12","$109,850"],
                 ["Vivian Harrell","Financial Controller","San Francisco","62","2009/02/14","$452,500"],
                 ["Timothy Mooney","Office Manager","London","37","2008/12/11","$136,200"],
                 ["Jackson Bradshaw","Director","New York","65","2008/09/26","$645,750"],
                 ["Olivia Liang","Support Engineer","Singapore","64","2011/02/03","$234,500"],
                 ["Bruno Nash","Software Engineer","London","38","2011/05/03","$163,500"],
                 ["Sakura Yamamoto","Support Engineer","Tokyo","37","2009/08/19","$139,575"],
                 ["Thor Walton","Developer","New York","61","2013/08/11","$98,540"],
                 ["Finn Camacho","Support Engineer","San Francisco","47","2009/07/07","$87,500"],
                 ["Serge Baldwin","Data Coordinator","Singapore","64","2012/04/09","$138,575"],
                 ["Zenaida Frank","Software Engineer","New York","63","2010/01/04","$125,250"],
                 ["Zorita Serrano","Software Engineer","San Francisco","56","2012/06/01","$115,000"],
                 ["Jennifer Acosta","Junior Javascript Developer","Edinburgh","43","2013/02/01","$75,650"],
                 ["Cara Stevens","Sales Assistant","New York","46","2011/12/06","$145,600"],
                 ["Hermione Butler","Regional Director","London","47","2011/03/21","$356,250"],
                 ["Lael Greer","Systems Administrator","London","21","2009/02/27","$103,500"],
                 ["Jonas Alexander","Developer","San Francisco","30","2010/07/14","$86,500"],
                 ["Shad Decker","Regional Director","Edinburgh","51","2008/11/13","$183,000"],
                 ["Michael Bruce","Javascript Developer","Singapore","29","2011/06/27","$183,000"],
                 ["Donna Snider","Customer Support","New York","27","2011/01/25","$112,000"]
           ],
             "columnDefs":[{
                     "title":"Full Name"
                   , "targets": [ 0 ]
               },{
                     "targets": [ 1 ]
                   , "sortable": true
               },{
                     "targets":[ 4 ]
                   , "type": "date"
                   , "render": function(date, type, full) {
                             return new Date(date).toDateString();
                   }  
               }]
    });
  })
  </script>
</body>
</html>

You can see, how it looks:

Testing Elasticsearch Installation

Getting Data from Server

The next step is to fetch data by making AJAX call to the server. We will use Rails app for getting JSON data. Add the rack-cors gem:

gem 'rack-cors'

to Gemfile and run bundle. Add cors.rb to config/initializers folder.

Rails.application.config.middleware.insert_before 0, Rack::Cors do
  allow do
    origins '127.0.0.1:8080'
    resource '*',
      headers: :any,
      methods: %i(get post put patch delete options head)
  end
end

I am running live-server on port 8080 that is a single page front-end app consisting of javascript, css and html. It looks like this:

<html>
<head>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
</head>
<body>

    <table id="example" class="display" style="width:100%">
    <thead>
        <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
        </tr>
    </thead>

        <tbody>
        </tbody>

    <tfoot>
        <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
        </tr>
    </tfoot>
  </table>

<script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.3.1.js"></script>
  <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
  <script>
        $(document).ready(function() {
            $('#example').DataTable( {
                "processing": true,
                "serverSide": true,
                "ajax": "http://localhost:3000/users/index.json"
            } );
        });

  </script>
</body>
</html>

You can see we are now using server side processing to fetch JSON data from the server. The Rails controller looks like this:

class UsersController < ApplicationController
  def index
        render json: {
            "pageLength": 10,
            "recordsTotal": 56,
            "recordsFiltered": 56,
            "data": [
             ["Tiger Nixon","System Architect","Edinburgh","61", "2011/04/25", "$320,800"],
                 ["Bradley Greer","Software Engineer","London","41","2012/10/13", "$132,000"],              
                 ["Dai Rios", "Personnel Lead", "Edinburgh", "35", "2012/09/26", "$217,500"],
                 ["Jenette Caldwell", "Development Lead", "New York", "30", "2011/09/03", "$345,000"],
                 ["Yuri Berry", "Chief Marketing Officer (CMO)","New York", "40", "2009/06/25", "$675,000"],
                 ["Caesar Vance", "Pre-Sales Support", "New York", "21", "2011/12/12", "$106,450"],
                 ["Doris Wilder", "Sales Assistant","Sidney","23","2010/09/20","$85,600"],
                 ["Angelica Ramos", "Chief Executive Officer (CEO)", "London","47","2009/10/09","$1,200,000"],
                 ["Gavin Joyce","Developer","Edinburgh","42","2010/12/22","$92,575"],
                 ["Jennifer Chang","Regional Director","Singapore","28","2010/11/14","$357,650"],
                 ["Brenden Wagner","Software Engineer","San Francisco","28","2011/06/07","$206,850"],
                 ["Fiona Green","Chief Operating Officer (COO)","San Francisco","48","2010/03/11","$850,000"],
                 ["Shou Itou","Regional Marketing","Tokyo","20","2011/08/14","$163,000"],
                 ["Michelle House","Integration Specialist","Sidney","37","2011/06/02","$ 95,400"],
                 ["Suki Burks","Developer","London","53","2009/10/22","$114,500"],
                 ["Prescott Bartlett","Technical Author","London","27","2011/05/07","$145,000"],
                 ["Gavin Cortez","Team Leader","San Francisco","22","2008/10/26","$235,500"],                
             ["Gloria Little","Systems Administrator","New York","59", "2009/04/10", "$237,500"],
             ["Paul Byrd","Chief Financial Officer (CFO)","New York","64", "2010/06/09", "$725,000"],
             ["Tatyana Fitzpatrick","Regional Director","London","19", "2010/03/17", "$385,750"],
                 ["Haley Kennedy","Senior Marketing Designer","London","43", "2012/12/18", "$313,500"],
             ["Charde Marshall","Regional Director","San Francisco","36", "2008/10/16", "$470,600"],
             ["Quinn Flynn","Support Lead","Edinburgh","22", "2013/03/03", "$342,000"],
             ["Jena Gaines","Office Manager","London","30", "2008/12/19", "$90,560"],
             ["Sonya Frost","Software Engineer","Edinburgh","23", "2008/12/13", "$103,600"],
             ["Colleen Hurst","Javascript Developer","San Francisco","39", "2009/09/15", "$205,500"],                                                                                                
             ["Rhona Davidson","Integration Specialist","Tokyo","55", "2010/10/14", "$327,900"],
                 ["Herrod Chandler","Sales Assistant","San Francisco","59", "2012/08/06", "$137,500"],
             ["Brielle Williamson","Integration Specialist","New York","61", "2012/12/02", "$372,000"],
             ["Airi Satou","Accountant","Tokyo","33", "2008/11/28", "$162,700"],
             ["Cedric Kelly","Senior Javascript Developer", "Edinburgh", "22","2012/03/29", "$433,060"],
             ["Ashton Cox","Junior Technical Author","San Francisco","66", "2009/01/12", "$86,000"],
             ["Garrett Winters","Accountant","Tokyo","63", "2011/07/25", "$170,750"],
                 ["Martena Mccray","Post-Sales support","Edinburgh","46","2011/03/09","$324,050"],
               ["Unity Butler","Marketing Designer","San Francisco","47","2009/12/09","$85,675"],
                 ["Howard Hatfield","Office Manager","San Francisco","51","2008/12/16","$164,500"],
                 ["Hope Fuentes","Secretary","San Francisco","41","2010/02/12","$109,850"],
                 ["Vivian Harrell","Financial Controller","San Francisco","62","2009/02/14","$452,500"],
                 ["Timothy Mooney","Office Manager","London","37","2008/12/11","$136,200"],
                 ["Jackson Bradshaw","Director","New York","65","2008/09/26","$645,750"],
                 ["Olivia Liang","Support Engineer","Singapore","64","2011/02/03","$234,500"],
                 ["Bruno Nash","Software Engineer","London","38","2011/05/03","$163,500"],
                 ["Sakura Yamamoto","Support Engineer","Tokyo","37","2009/08/19","$139,575"],
                 ["Thor Walton","Developer","New York","61","2013/08/11","$98,540"],
                 ["Finn Camacho","Support Engineer","San Francisco","47","2009/07/07","$87,500"],
                 ["Serge Baldwin","Data Coordinator","Singapore","64","2012/04/09","$138,575"],
                 ["Zenaida Frank","Software Engineer","New York","63","2010/01/04","$125,250"],
                 ["Zorita Serrano","Software Engineer","San Francisco","56","2012/06/01","$115,000"],
                 ["Jennifer Acosta","Junior Javascript Developer","Edinburgh","43","2013/02/01","$75,650"],
                 ["Cara Stevens","Sales Assistant","New York","46","2011/12/06","$145,600"],
                 ["Hermione Butler","Regional Director","London","47","2011/03/21","$356,250"],
                 ["Lael Greer","Systems Administrator","London","21","2009/02/27","$103,500"],
                 ["Jonas Alexander","Developer","San Francisco","30","2010/07/14","$86,500"],
                 ["Shad Decker","Regional Director","Edinburgh","51","2008/11/13","$183,000"],
                 ["Michael Bruce","Javascript Developer","Singapore","29","2011/06/27","$183,000"],
                 ["Donna Snider","Customer Support","New York","27","2011/01/25","$112,000"]
            ]
        }

  end
end

The page displays all the 56 records, it ignores the pageLength option we have set to 10. The reason is that we need to handle the pagination on the server side. The view displays whatever the server sends.

Parameters from Client

Clicking on the first page, we see the parameters are:

{
    "draw" => "3", "columns" => {
        "0" => {
            "data" => "0", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }, "1" => {
            "data" => "1", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }, "2" => {
            "data" => "2", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }, "3" => {
            "data" => "3", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }, "4" => {
            "data" => "4", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }, "5" => {
            "data" => "5", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }
    }, "order" => {
        "0" => {
            "column" => "0", "dir" => "asc"
        }
    }, "start" => "0", "length" => "10", "search" => {
        "value" => "", "regex" => "false"
    }, "_" => "1538204747378"
}

Clicking on the second page, we see the parameters are:

 {
    "draw" => "2", "columns" => {
        "0" => {
            "data" => "0", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }, "1" => {
            "data" => "1", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }, "2" => {
            "data" => "2", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }, "3" => {
            "data" => "3", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }, "4" => {
            "data" => "4", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }, "5" => {
            "data" => "5", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                "value" => "", "regex" => "false"
            }
        }
    }, "order" => {
        "0" => {
            "column" => "0", "dir" => "asc"
        }
    }, "start" => "10", "length" => "10", "search" => {
        "value" => "", "regex" => "false"
    }, "_" => "1538204747377"
 }

The parameters relevant to pagination are the start and length. According the Datatables Server-side Processing docs, start is:

Paging first record indicator. This is the start point in the current data set (0 index based - i.e. 0 is the first record).

and the length is:

Number of records that the table can display in the current draw. It is expected that the number of records returned will be equal to this number, unless the server has fewer records to return.

We also see the column ordering asc or desc. We must return atleast three parameters, recordsTotal, recordsFiltered and data as described in the docs. We must convert the string to integer for start and length in the params.

 params['start'].to_i
 params['length'].to_i

We have learned enough by reading the Datatables documentation and looking at the Rails console the params sent by the client to start writing tests. Let's document these requirements as tests:

require 'minitest/autorun'

class Tabular
  def initialize(params)
    @params = params
  end

  def start
    @params['start'].to_i
  end

  def length
    @params['length'].to_i
  end
end

describe Tabular do

    it 'should convert the string to integer for start' do
      tabular = Tabular.new(first_page_parameters)
    start = tabular.start       

    assert_equal 0, start
    end

    it 'should convert the string to integer for length' do
      tabular = Tabular.new(first_page_parameters)
    length = tabular.length     

    assert_equal 10, length
    end

    def first_page_parameters
    {
        "draw" => "3", "columns" => {
            "0" => {
                "data" => "0", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "1" => {
                "data" => "1", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "2" => {
                "data" => "2", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "3" => {
                "data" => "3", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "4" => {
                "data" => "4", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "5" => {
                "data" => "5", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }
        }, "order" => {
            "0" => {
                "column" => "0", "dir" => "asc"
            }
        }, "start" => "0", "length" => "10", "search" => {
            "value" => "", "regex" => "false"
        }, "_" => "1538204747378"
    }
    end
end

Rails will allow us to use symbols instead of string:

 params[:start].to_i
 params[:length].to_i

This is a minor change, we can tackle it towards the end. Let's focus on the requirements that is independent of the Rails framework. This allows us to write tests outside of Rails framework.

Pages

We can default to 10 records per page if length parameter is negative (this will be the case for returning all the records, we don't want that to happen). The corresponding tests and the code to implement the requirements:

require 'minitest/autorun'

class Tabular

  def records_per_page
    length = @params['length'].to_i
    length > 0 ? length : DEFAULT_RECORDS_PER_PAGE
  end

  def page_number
    @params['start'].to_i / records_per_page + 1
  end
end

describe Tabular do

  it 'should calculate the page number for first page' do
    tabular = Tabular.new(first_page_parameters)  
    page_number = tabular.page_number

    assert_equal 1, page_number
  end

  it 'should calculate the page number for second page' do
    tabular = Tabular.new(second_page_parameters)  
    page_number = tabular.page_number

    assert_equal 2, page_number
  end

    def first_page_parameters
    {
        "draw" => "3", 
      "columns" => {
            "0" => {
                "data" => "0", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "1" => {
                "data" => "1", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "2" => {
                "data" => "2", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "3" => {
                "data" => "3", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "4" => {
                "data" => "4", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "5" => {
                "data" => "5", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }
        }, 
      "order" => {
            "0" => {
                "column" => "0", "dir" => "asc"
            }
        }, 
      "start" => "0", 
      "length" => "10", 
      "search" => {
            "value" => "", "regex" => "false"
        }, "_" => "1538204747378"
    }
    end

  def second_page_parameters
    {
        "draw" => "2", 
      "columns" => {
            "0" => {
                "data" => "0", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "1" => {
                "data" => "1", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "2" => {
                "data" => "2", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "3" => {
                "data" => "3", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "4" => {
                "data" => "4", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }, "5" => {
                "data" => "5", "name" => "", "searchable" => "true", "orderable" => "true", "search" => {
                    "value" => "", "regex" => "false"
                }
            }
        }, 
      "order" => {
            "0" => {
                "column" => "0", "dir" => "asc"
            }
        }, 
      "start" => "10", 
      "length" => "10", 
      "search" => {
            "value" => "", "regex" => "false"
        }, "_" => "1538204747377"
    }
  end
end

Sort

Sort column is in order parameter:

params['order']
 => {"0"=>{"column"=>"0", "dir"=>"asc"}}

In this case, we only have one column:

 params['order']['0']['column']
  => "0"

Sort direction:

 params['order']['0']['dir']
 => "asc" 

In this case, the first column is in ascending order. We can use this in ActiveRecord:

users = User.order("#{sort_column} #{sort_direction}")

We can add the tests for it:

  it 'should parse the sort column' do
    tabular = Tabular.new(first_page_parameters)
    sort_column = tabular.sort_column

    assert_equal '0', sort_column
  end

  it 'should parse the sort direction' do
    tabular = Tabular.new(first_page_parameters)
    sort_direction = tabular.sort_direction

    assert_equal 'asc', sort_direction  
  end

The implementation is simple:

  def sort_column
    @params['order']['0']['column']
  end

  def sort_direction
    @params['order']['0']['dir']
  end

Searching

To handle the search term entered in the search text field, we can retrieve the value entered:

params['search']['value']

search key consists of the hash:

{"value"=>"", "regex"=>"false"} 

The value will be blank string when it is not a search request. The tests and the implementation are shown below:

  it 'should parse the search term' do
    tabular = Tabular.new(search_parameters)
    search_term = tabular.search_term

    assert_equal 'Testing', search_term
  end
  def search_term
    @params['search']['value']
  end

Offset

With one page having 10 items:

page 1 will have items 1 - 10
page 2 ............... 11 - 20
page 3 ............... 21 - 30

The offsets must be:

Page 1 offset = 0
Page 2 offset = 10
Page 3 offset = 20

The tests:

  it 'page offset is 0 for first page' do
    tabular = Tabular.new(first_page_parameters)
    offset = tabular.offset

    assert_equal 0, offset    
  end

  it 'page offset is 10 for the second page with 10 records per page' do
    tabular = Tabular.new(second_page_parameters)
    offset = tabular.offset

    assert_equal 10, offset        
  end

The implementation to calculate offset:

  def offset
    (page_number - 1) * records_per_page
  end

Pagination

We can limit by:

User.limit(10)

We can chain limit and offset in the controller:

tabular = Tabular.new(params)
User.limit(tabular.records_per_page).offset(tabular.offset)

We can now get data for the data section of the JSON response from the server without using Kaminari or will_paginate gem:

 def fetch_users
    search_string = []
    columns.each do |term|
      search_string << "#{term} like :search"
    end

    users = User.order("#{tabular.sort_column} #{tabular.sort_direction}")
    users = users.limit(tabular.records_per_page).offset(tabular.offset)
    users = users.where(search_string.join(' or '), search: "%#{tabular.search_term}%")
  end

  def columns
    %w(name email city)
  end

  def as_json(options = {})
    {
      recordsTotal: User.count,
      recordsFiltered: 'count the number of records after applying where conditions',
      data: data
    }
  end

Let's modify the controller to get data from the database:

class UsersController < ApplicationController
  def index
        data = []
        User.all.each do |user|
            data << ["#{user.first_name} #{user.last_name}", user.title, user.city, user.age, "#{user.start_date.strftime('%Y/%m/%d')}", user.salary]
        end
        render json: {
            "pageLength": 10,
            "recordsTotal": 100,
            "recordsFiltered": 100,
            "data": data
        }
  end
end

The seeds.rb populates the database using Faker gem:

100.times do
  User.create(first_name: Faker::Name.first_name,
              last_name: Faker::Name.last_name,
              title: Faker::Job.title,
              city: Faker::Address.city,
              age: rand(18..65),
              start_date: Faker::Date.birthday(1, 35).strftime('%Y/%m/%d'),
              salary: "$" + "#{rand(25000..200000)}")
end

Here is the quick and dirty version that implements search, pagination and sorting:

class UsersController < ApplicationController
  def index
        tabular = Tabular.new(params)

        search_string = []
        columns = %w(first_name last_name title city age start_date salary)
        columns.each do |term|
            search_string << "#{term} like :search"
        end

        table_headers = %w(first_name title city age start_date salary)
        sort_column = table_headers[tabular.sort_column]
        users = User.order("#{sort_column} #{tabular.sort_direction}")
        users = users.limit(tabular.records_per_page).offset(tabular.offset)
        users = users.where(search_string.join(' or '), search: "%#{tabular.search_term}%")
        records_filtered = users.count

        data = []
        users.each do |user|
            data << ["#{user.first_name} #{user.last_name}", user.title, user.city, user.age, "#{user.start_date.strftime('%Y/%m/%d')}", user.salary]
        end

        render json: {
            "pageLength": tabular.records_per_page,
            "recordsTotal": User.count,
            "recordsFiltered": 100,
            "data": data
        }
  end
end

The next step is to implement inplace editing. You can download the Rails project for this app from tabular.

Gotchas

From Datatables forum thread:

The recordsFiltered has a value different of recordsTotal. The recordsFiltred is the number of items after globalSearch or columnSearch. If there is no search (globalSearch or columnSearch) , then the value recordsFiltered is equal to recordsTotal.

From Datatables docs:

recordsFiltered is the total number of records in the data set after filtering - not just the data array length (which DataTables can do itself!). So if you have no filtering recordsFiltered should be exactly the same as recordsTotal

Implementing Datatables without Kaminari gives developers who are working on projects where they don't have the freedom to choose or upgrade the existing pagination gems. It can also be due to version upgrade constraints.

Refactored Solution

UserDatatable:

class UserDatatable
  COLUMNS = %w(first_name last_name title city age start_date salary)
  TABLE_HEADERS = %w(first_name title city age start_date salary)

  def initialize(hash)
    @tabular = Tabular.new(hash)
    @search_string = []
    COLUMNS.each do |term|
        @search_string << "#{term} like :search"
    end
  end  

  def data
    sort_column = TABLE_HEADERS[@tabular.sort_column]

    users = User.order("#{sort_column} #{@tabular.sort_direction}")
    users = users.limit(page_length).offset(@tabular.offset)
    users = users.where(@search_string.join(' or '), search: "%#{@tabular.search_term}%")

    result = []
    users.each do |user|
        result << ["#{user.first_name} #{user.last_name}", user.title, user.city, user.age, "#{user.start_date.strftime('%Y/%m/%d')}", user.salary]
    end    
    result
  end

  def page_length
    @tabular.records_per_page
  end

  def records_filtered
    100
  end

  def records_total
    User.count
  end
end

Controller:

class UsersController < ApplicationController
  def index
    data_table = UserDatatable.new(params)

    render json: {
            "pageLength": data_table.page_length,
            "recordsTotal": data_table.records_total,
            "recordsFiltered": data_table.records_filtered,
            "data": data_table.data
    }
  end
end

Parameters parser:

class Tabular
  DEFAULT_RECORDS_PER_PAGE = 10

  def initialize(params)
    @params = params
  end

  def start
    @params['start'].to_i
  end

  def length
    @params['length'].to_i
  end

  def records_per_page
    length = @params['length'].to_i
    length > 0 ? length : DEFAULT_RECORDS_PER_PAGE
  end

  def page_number
    @params['start'].to_i / records_per_page + 1
  end

  def sort_column
    @params['order']['0']['column'].to_i
  end

  def sort_direction
    @params['order']['0']['dir']
  end

  def search_term
    @params['search']['value']
  end

  def offset
    (page_number - 1) * records_per_page
  end
end

Using Twitter Bootstrap

Testing Elasticsearch Installation

The html page:

<html>
<head>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap.min.css">
    <link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">    
</head>
<body>

    <div class='container' style='padding-top: 20px'>
        <table id="example" class="table table-striped table-bordered table-hover" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start Date</th>
                <th>Salary</th>
            </tr>
        </thead>

            <tbody>
            </tbody>

        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
      </table>
    </div>

  <script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.3.1.js"></script>
  <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap.min.js"></script>

  <script>
        $(document).ready(function() {
            $('#example').DataTable( {
                        "pageLength": 10,
                "processing": true,
                "serverSide": true,
                "ajax": "http://localhost:3000/users/index.json"
            } );
        });

  </script>
</body>
</html>

References


Related Articles


Ace the Technical Interview

  • Easily find the gaps in your knowledge
  • Get customized lessons based on where you are
  • Take consistent action everyday
  • Builtin accountability to keep you on track
  • You will solve bigger problems over time
  • Get the job of your dreams

Take the 30 Day Coding Skills Challenge

Gain confidence to attend the interview

No spam ever. Unsubscribe anytime.