Using Join Tables with Datatables in Rails

The javascript uses ajax call to get the JSON data from the server. We are using jsonp to circumvent the CORS error. You will get

Access-Control-Allow-Origin' header has a value 'null' that is not equal to the supplied origin. Origin 'null' is therefore not allowed access. json-server

if you don't provide the crossDomain and the dataType values. The front end app consists of the javascript and html files.

The index.html file:

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title></title>
  <meta name="description" content="">
  <meta name="author" content="">
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.17/css/jquery.dataTables.min.css">
  <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.5.2/css/buttons.dataTables.min.css">
  <link rel="stylesheet" href="https://cdn.datatables.net/select/1.2.6/css/select.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>Extn.</th>
        <th>Start date</th>
        <th>Salary</th>
        </tr>
    </thead>
  </table>

  <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
  <script src="https://cdn.datatables.net/1.10.17/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.5.2/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/select/1.2.6/js/dataTables.select.min.js"></script>
  <script src="./app.js"></script>

</body>
</html>

The app.js is shown below:

$(document).ready(function() {
    $('#example').DataTable( {
        "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
        "ajax": {
            "url": "http://localhost:3000/data",
            "dataSrc": "",
            "crossDomain": true,
            "dataType": "jsonp" 
        },
        "columns": [
            { "data": "name" },
            { "data": "hr.position" },
            { "data": "contact.0" },
            { "data": "contact.1" },
            { "data": "hr.start_date" },
            { "data": "hr.salary" }
        ]
    } );
} );

We can customize the drop-down to select number of records per page:

"lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]]

You can see how we navigate the nested data by using the object notation to traverse the JSON data. You can also see how to navigate the elements in an array. For instance, the first element in the contact array is accessed by contact.0. The JSON data structure is as shown below:

{
  "data": [
    {
      "name": "Tiger Nixon",
      "hr": {
        "position": "System Architect",
        "salary": "$320,800",
        "start_date": "2011/04/25"
      },
      "contact": [
        "Edinburgh",
        "5421"
      ]
    },
    {
      "name": "Garrett Winters",
      "hr": {
        "position": "Accountant",
        "salary": "$170,750",
        "start_date": "2011/07/25"
      },
      "contact": [
        "Tokyo",
        "8422"
      ]
    },
    {
      "name": "Ashton Cox",
      "hr": {
        "position": "Junior Technical Author",
        "salary": "$86,000",
        "start_date": "2009/01/12"
      },
      "contact": [
        "San Francisco",
        "1562"
      ]
    },
    {
      "name": "Cedric Kelly",
      "hr": {
        "position": "Senior Javascript Developer",
        "salary": "$433,060",
        "start_date": "2012/03/29"
      },
      "contact": [
        "Edinburgh",
        "6224"
      ]
    },
    {
      "name": "Airi Satou",
      "hr": {
        "position": "Accountant",
        "salary": "$162,700",
        "start_date": "2008/11/28"
      },
      "contact": [
        "Tokyo",
        "5407"
      ]
    },
    {
      "name": "Brielle Williamson",
      "hr": {
        "position": "Integration Specialist",
        "salary": "$372,000",
        "start_date": "2012/12/02"
      },
      "contact": [
        "New York",
        "4804"
      ]
    },
    {
      "name": "Herrod Chandler",
      "hr": {
        "position": "Sales Assistant",
        "salary": "$137,500",
        "start_date": "2012/08/06"
      },
      "contact": [
        "San Francisco",
        "9608"
      ]
    },
    {
      "name": "Rhona Davidson",
      "hr": {
        "position": "Integration Specialist",
        "salary": "$327,900",
        "start_date": "2010/10/14"
      },
      "contact": [
        "Tokyo",
        "6200"
      ]
    },
    {
      "name": "Colleen Hurst",
      "hr": {
        "position": "Javascript Developer",
        "salary": "$205,500",
        "start_date": "2009/09/15"
      },
      "contact": [
        "San Francisco",
        "2360"
      ]
    },
    {
      "name": "Sonya Frost",
      "hr": {
        "position": "Software Engineer",
        "salary": "$103,600",
        "start_date": "2008/12/13"
      },
      "contact": [
        "Edinburgh",
        "1667"
      ]
    },
    {
      "name": "Jena Gaines",
      "hr": {
        "position": "Office Manager",
        "salary": "$90,560",
        "start_date": "2008/12/19"
      },
      "contact": [
        "London",
        "3814"
      ]
    },
    {
      "name": "Quinn Flynn",
      "hr": {
        "position": "Support Lead",
        "salary": "$342,000",
        "start_date": "2013/03/03"
      },
      "contact": [
        "Edinburgh",
        "9497"
      ]
    },
    {
      "name": "Charde Marshall",
      "hr": {
        "position": "Regional Director",
        "salary": "$470,600",
        "start_date": "2008/10/16"
      },
      "contact": [
        "San Francisco",
        "6741"
      ]
    },
    {
      "name": "Haley Kennedy",
      "hr": {
        "position": "Senior Marketing Designer",
        "salary": "$313,500",
        "start_date": "2012/12/18"
      },
      "contact": [
        "London",
        "3597"
      ]
    },
    {
      "name": "Tatyana Fitzpatrick",
      "hr": {
        "position": "Regional Director",
        "salary": "$385,750",
        "start_date": "2010/03/17"
      },
      "contact": [
        "London",
        "1965"
      ]
    },
    {
      "name": "Michael Silva",
      "hr": {
        "position": "Marketing Designer",
        "salary": "$198,500",
        "start_date": "2012/11/27"
      },
      "contact": [
        "London",
        "1581"
      ]
    },
    {
      "name": "Paul Byrd",
      "hr": {
        "position": "Chief Financial Officer (CFO)",
        "salary": "$725,000",
        "start_date": "2010/06/09"
      },
      "contact": [
        "New York",
        "3059"
      ]
    },
    {
      "name": "Gloria Little",
      "hr": {
        "position": "Systems Administrator",
        "salary": "$237,500",
        "start_date": "2009/04/10"
      },
      "contact": [
        "New York",
        "1721"
      ]
    },
    {
      "name": "Bradley Greer",
      "hr": {
        "position": "Software Engineer",
        "salary": "$132,000",
        "start_date": "2012/10/13"
      },
      "contact": [
        "London",
        "2558"
      ]
    },
    {
      "name": "Dai Rios",
      "hr": {
        "position": "Personnel Lead",
        "salary": "$217,500",
        "start_date": "2012/09/26"
      },
      "contact": [
        "Edinburgh",
        "2290"
      ]
    },
    {
      "name": "Jenette Caldwell",
      "hr": {
        "position": "Development Lead",
        "salary": "$345,000",
        "start_date": "2011/09/03"
      },
      "contact": [
        "New York",
        "1937"
      ]
    },
    {
      "name": "Yuri Berry",
      "hr": {
        "position": "Chief Marketing Officer (CMO)",
        "salary": "$675,000",
        "start_date": "2009/06/25"
      },
      "contact": [
        "New York",
        "6154"
      ]
    },
    {
      "name": "Caesar Vance",
      "hr": {
        "position": "Pre-Sales Support",
        "salary": "$106,450",
        "start_date": "2011/12/12"
      },
      "contact": [
        "New York",
        "8330"
      ]
    },
    {
      "name": "Doris Wilder",
      "hr": {
        "position": "Sales Assistant",
        "salary": "$85,600",
        "start_date": "2010/09/20"
      },
      "contact": [
        "Sidney",
        "3023"
      ]
    },
    {
      "name": "Angelica Ramos",
      "hr": {
        "position": "Chief Executive Officer (CEO)",
        "salary": "$1,200,000",
        "start_date": "2009/10/09"
      },
      "contact": [
        "London",
        "5797"
      ]
    },
    {
      "name": "Gavin Joyce",
      "hr": {
        "position": "Developer",
        "salary": "$92,575",
        "start_date": "2010/12/22"
      },
      "contact": [
        "Edinburgh",
        "8822"
      ]
    },
    {
      "name": "Jennifer Chang",
      "hr": {
        "position": "Regional Director",
        "salary": "$357,650",
        "start_date": "2010/11/14"
      },
      "contact": [
        "Singapore",
        "9239"
      ]
    },
    {
      "name": "Brenden Wagner",
      "hr": {
        "position": "Software Engineer",
        "salary": "$206,850",
        "start_date": "2011/06/07"
      },
      "contact": [
        "San Francisco",
        "1314"
      ]
    },
    {
      "name": "Fiona Green",
      "hr": {
        "position": "Chief Operating Officer (COO)",
        "salary": "$850,000",
        "start_date": "2010/03/11"
      },
      "contact": [
        "San Francisco",
        "2947"
      ]
    },
    {
      "name": "Shou Itou",
      "hr": {
        "position": "Regional Marketing",
        "salary": "$163,000",
        "start_date": "2011/08/14"
      },
      "contact": [
        "Tokyo",
        "8899"
      ]
    },
    {
      "name": "Michelle House",
      "hr": {
        "position": "Integration Specialist",
        "salary": "$95,400",
        "start_date": "2011/06/02"
      },
      "contact": [
        "Sidney",
        "2769"
      ]
    },
    {
      "name": "Suki Burks",
      "hr": {
        "position": "Developer",
        "salary": "$114,500",
        "start_date": "2009/10/22"
      },
      "contact": [
        "London",
        "6832"
      ]
    },
    {
      "name": "Prescott Bartlett",
      "hr": {
        "position": "Technical Author",
        "salary": "$145,000",
        "start_date": "2011/05/07"
      },
      "contact": [
        "London",
        "3606"
      ]
    },
    {
      "name": "Gavin Cortez",
      "hr": {
        "position": "Team Leader",
        "salary": "$235,500",
        "start_date": "2008/10/26"
      },
      "contact": [
        "San Francisco",
        "2860"
      ]
    },
    {
      "name": "Martena Mccray",
      "hr": {
        "position": "Post-Sales support",
        "salary": "$324,050",
        "start_date": "2011/03/09"
      },
      "contact": [
        "Edinburgh",
        "8240"
      ]
    },
    {
      "name": "Unity Butler",
      "hr": {
        "position": "Marketing Designer",
        "salary": "$85,675",
        "start_date": "2009/12/09"
      },
      "contact": [
        "San Francisco",
        "5384"
      ]
    },
    {
      "name": "Howard Hatfield",
      "hr": {
        "position": "Office Manager",
        "salary": "$164,500",
        "start_date": "2008/12/16"
      },
      "contact": [
        "San Francisco",
        "7031"
      ]
    },
    {
      "name": "Hope Fuentes",
      "hr": {
        "position": "Secretary",
        "salary": "$109,850",
        "start_date": "2010/02/12"
      },
      "contact": [
        "San Francisco",
        "6318"
      ]
    },
    {
      "name": "Vivian Harrell",
      "hr": {
        "position": "Financial Controller",
        "salary": "$452,500",
        "start_date": "2009/02/14"
      },
      "contact": [
        "San Francisco",
        "9422"
      ]
    },
    {
      "name": "Timothy Mooney",
      "hr": {
        "position": "Office Manager",
        "salary": "$136,200",
        "start_date": "2008/12/11"
      },
      "contact": [
        "London",
        "7580"
      ]
    },
    {
      "name": "Jackson Bradshaw",
      "hr": {
        "position": "Director",
        "salary": "$645,750",
        "start_date": "2008/09/26"
      },
      "contact": [
        "New York",
        "1042"
      ]
    },
    {
      "name": "Olivia Liang",
      "hr": {
        "position": "Support Engineer",
        "salary": "$234,500",
        "start_date": "2011/02/03"
      },
      "contact": [
        "Singapore",
        "2120"
      ]
    },
    {
      "name": "Bruno Nash",
      "hr": {
        "position": "Software Engineer",
        "salary": "$163,500",
        "start_date": "2011/05/03"
      },
      "contact": [
        "London",
        "6222"
      ]
    },
    {
      "name": "Sakura Yamamoto",
      "hr": {
        "position": "Support Engineer",
        "salary": "$139,575",
        "start_date": "2009/08/19"
      },
      "contact": [
        "Tokyo",
        "9383"
      ]
    },
    {
      "name": "Thor Walton",
      "hr": {
        "position": "Developer",
        "salary": "$98,540",
        "start_date": "2013/08/11"
      },
      "contact": [
        "New York",
        "8327"
      ]
    },
    {
      "name": "Finn Camacho",
      "hr": {
        "position": "Support Engineer",
        "salary": "$87,500",
        "start_date": "2009/07/07"
      },
      "contact": [
        "San Francisco",
        "2927"
      ]
    },
    {
      "name": "Serge Baldwin",
      "hr": {
        "position": "Data Coordinator",
        "salary": "$138,575",
        "start_date": "2012/04/09"
      },
      "contact": [
        "Singapore",
        "8352"
      ]
    },
    {
      "name": "Zenaida Frank",
      "hr": {
        "position": "Software Engineer",
        "salary": "$125,250",
        "start_date": "2010/01/04"
      },
      "contact": [
        "New York",
        "7439"
      ]
    },
    {
      "name": "Zorita Serrano",
      "hr": {
        "position": "Software Engineer",
        "salary": "$115,000",
        "start_date": "2012/06/01"
      },
      "contact": [
        "San Francisco",
        "4389"
      ]
    },
    {
      "name": "Jennifer Acosta",
      "hr": {
        "position": "Junior Javascript Developer",
        "salary": "$75,650",
        "start_date": "2013/02/01"
      },
      "contact": [
        "Edinburgh",
        "3431"
      ]
    },
    {
      "name": "Cara Stevens",
      "hr": {
        "position": "Sales Assistant",
        "salary": "$145,600",
        "start_date": "2011/12/06"
      },
      "contact": [
        "New York",
        "3990"
      ]
    },
    {
      "name": "Hermione Butler",
      "hr": {
        "position": "Regional Director",
        "salary": "$356,250",
        "start_date": "2011/03/21"
      },
      "contact": [
        "London",
        "1016"
      ]
    },
    {
      "name": "Lael Greer",
      "hr": {
        "position": "Systems Administrator",
        "salary": "$103,500",
        "start_date": "2009/02/27"
      },
      "contact": [
        "London",
        "6733"
      ]
    },
    {
      "name": "Jonas Alexander",
      "hr": {
        "position": "Developer",
        "salary": "$86,500",
        "start_date": "2010/07/14"
      },
      "contact": [
        "San Francisco",
        "8196"
      ]
    },
    {
      "name": "Shad Decker",
      "hr": {
        "position": "Regional Director",
        "salary": "$183,000",
        "start_date": "2008/11/13"
      },
      "contact": [
        "Edinburgh",
        "6373"
      ]
    },
    {
      "name": "Michael Bruce",
      "hr": {
        "position": "Javascript Developer",
        "salary": "$183,000",
        "start_date": "2011/06/27"
      },
      "contact": [
        "Singapore",
        "5384"
      ]
    },
    {
      "name": "Donna Snider",
      "hr": {
        "position": "Customer Support",
        "salary": "$112,000",
        "start_date": "2011/01/25"
      },
      "contact": [
        "New York",
        "4226"
      ]
    }
  ]
}

You can save this as .json file with any file name for the backend app in a different folder. The javascript uses the root element, in this case data in the URL to make AJAX call. You can install json-server to serve this JSON file as the response to the front end app request. The json-server is a fake REST API server that is easy to setup. Install it:

$ npm install -g json-server

Run it from the backend app folder.

$ json-server --watch data.json

In this case, the name of the .json file in the backend app project folder is data.json. You can curl or hit the URL see in the server log to make sure there is no JSON format error.

Using Rails as the Backend Server

You have to define an enpoint that can handle the JSON request from the client. You can create the data needed for the front-end by looping through the model. You have to use the includes option like this:

Article.includes(:comments, :authors)

The structure we need is:

data = [ {}, {} ]

Each hash will consist of the nested objects as seen in the JSON structure. It's then as simple as doing:

render json: data

Notice that all the records are sent to the client, even if it is thousands of records. You can use low-level caching to improve the performance.

Using Bootstrap Theme with Datatables

Modify the index.html file:

<!doctype html>

<html lang="en">
<head>
  <meta charset="utf-8">

  <title></title>
  <meta name="description" content="">
  <meta name="author" content="">

  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap.min.css">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.5.2/css/buttons.dataTables.min.css">
  <link rel="stylesheet" href="https://cdn.datatables.net/select/1.2.6/css/select.dataTables.min.css">

</head>

<body>

  <div class='container' style='padding-top: 10px'>
    <table id="example" class="table table-striped" style="width:100%">
      <thead>
        <tr>
          <th>Name</th>
          <th>Position</th>
          <th>Office</th>
          <th>Extn.</th>
          <th>Start date</th>
          <th>Salary</th>
        </tr>
      </thead>

    </table>
  </div>

  <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
  <script src="https://cdn.datatables.net/1.10.17/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.5.2/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/select/1.2.6/js/dataTables.select.min.js"></script>
  <script src="./app.js"></script>

</body>
</html>

This uses Bootstrap 3.3.7, you can point to the Bootstrap 4 if you would like to use the latest version.


Related Articles


Create your own user feedback survey