Hey! Glad to see you again! Remember how in the previous parts of this tutorial we added DataTables plug-in to Rails application and set up static and Ajax-sourced tables? In this last part, we’re going to set up another table and get familiar with Server-side processing.
As it was mentioned before, Server-side processing comes to the rescue when your table operates with millions of records and reading data from the DOM becomes unbearably slow. In this case, all you can do to avoid such a nightmare is performing searching, ordering, and pagination on backend, before you stick the data into the DOM.
Today we’ll write mostly Ruby on Rails code. For me, it’s even better because I personally like backend more than frontend with its Javascript, HTML and CSS. Anyway, let’s get started!
As usual, you can continue with the code you got after the previous part or start from here. If you want to get a sneak peek at the result of this part look here.
How to deal with Server-side processing
Funny how we can duplicate almost all the code that had been written for the Ajax-sourced table and use it for a brand new table we’re gonna serve with Server-side processing. Really, just add one more route to config/routes.rb
get 'get_processed_dataset', to: 'pages#get_processed_dataset'
One more action to app/controllers/pages_controller.rb
def get_dataset
render json: { lines: Line.all }
end
One more table to app/views/pages/home.html.erb
<div class="container table-container">
<div class="row">
<div class="col-md-8 col-md-offset-2">
<table id="server-side-table" class="display">
</table>
</div>
</div>
</div>
And one more DataTable constructor to app/assets/javascripts/pages.js. OK, this constructor won’t be exactly the same as the one for "ajax-table". We will add serverSide property set to true and of course, will change url so that it matches the new action. But the rest is the same
$('#server-side-table').DataTable({
ajax: {
url: '/get_processed_dataset',
dataSrc: 'lines',
},
serverSide: true,
columns: ,
order: ]
});
Restart Rails server and check out the result. Looks good! Oh, wait… Not really good. Not good at all.
It seems all 514 records are going on the first page while there are at least 5 pages and also it says “Showing 0 to 0 of 0 entries (filtered from NaN total entries)” which is just silly. More to come – search is not working at all.
The thing is when Server-side processing is enabled, DataTables sends a bunch of additional parameters within Ajax request. And to handle searching, ordering, and pagination we have to return additional parameters along with the dataset back from the controller action too.
Hate to bombard you with huge chunks of code, but I have no choice. Here are the parameters come to our action from DataTables:
{
"draw"=>"1",
"columns"=>{
"0"=>{
"data"=>"season",
"name"=>"",
"searchable"=>"true",
"orderable"=>"true",
"search"=>{
"value"=>"",
"regex"=>"false"
}
},
"1"=>{
"data"=>"episode",
"name"=>"",
"searchable"=>"true",
"orderable"=>"true",
"search"=>{
"value"=>"",
"regex"=>"false"
}
},
"2"=>{
"data"=>"character",
"name"=>"",
"searchable"=>"true",
"orderable"=>"true",
"search"=>{
"value"=>"",
"regex"=>"false"
}
},
"3"=>{
"data"=>"line",
"name"=>"",
"searchable"=>"true",
"orderable"=>"true",
"search"=>{
"value"=>"",
"regex"=>"false"
}
}
},
"order"=>{
"0"=>{
"column"=>"2",
"dir"=>"desc"
}
},
"start"=>"0",
"length"=>"10",
"search"=>{
"value"=>"",
"regex"=>"false"
},
"_"=>"1534344124743"
}
What to notice here? The parameters contain draw counter which surprisingly counts the number of draws i.e. actions performed on the table until page reloads. I personally have never seen a problem with this counter but the docs strictly order to send it back to DataTables every time as an integer. And who are we to disobey?
There’s also columns parameter which contains a signature of every column including the flags which show if the column is searchable/orderable or not. That’s the columns we’ll have to set up searching and ordering for later.
There’s the info about ordering under order key. As you can see now, it tells that the table is ordered by column “2” (which is Character) and the ordering direction is “Desc”. Seems to be correct because if you remember, we initialized our table with default descending ordering by Character column.
The next parameters are start which stands for the number of the first record that must be shown and length which is basically the number of records on a page (10 by default).
And the last parameter we’re interested in is search. It contains the value that we should search for in all the searchable columns and the regex flag which tells whether we should search for it as a regular expression or as just a plain string.
What else should we return along with the dataset and draw counter? There are several parameters in the docs but we’ll be good with only two of them: recordsTotal as the total number of records in our database and recordsFiltered as the number of records left after search was performed.
So let’s code a little! Update get_processed_dataset action so that it looks like
def get_processed_dataset
lines = Line.datatable_filter(params, params)
lines_filtered = lines.count
render json: { lines: lines,
draw: params.to_i,
recordsTotal: Line.count,
recordsFiltered: lines_filtered }
end
What do we do here? We take search value params and columns signatures params from params and use them to perform datatable_filter on our lines. Then we count the number of the lines we got after filtering.
Eventually, we render the filtered lines, draw counter which we just took from params and converted to the integer, the total number of lines we have in our database and the number of filtered lines.
Everything should be great by now except we don’t have datatable_filter method implemented. That’s a shame. Let’s fix this by adding the following code to app/models/line.rb
DATATABLE_COLUMNS = %w.freeze
class << self
def datatable_filter(search_value, search_columns)
return all if search_value.blank?
result = none
search_columns.each do |key, value|
filter = where("#{DATATABLE_COLUMNS} ILIKE ?", "%#{search_value}%")
result = result.or(filter) if value
end
result
end
end
We defined datatable_filter class method which consumes the search value and the columns signatures. If search_value is blank, it simply returns all the lines we have in the database. Otherwise, it goes through the passed columns signatures, constructs the query by matching search_value against every searchable column and returns the resulting query.
I defined DATATABLE_COLUMNS constant to match the columns we use in the datatable against their indexes as they’re matching by DataTables.
Let’s look at our table now.
It still puts all the records on a single page, the order is random but the search is working now. And the line below the table shows correct info about how many lines we have in total and how many of them were filtered. That’s what I call progress! Let’s not stop!
To fix ordering update get_processed_dataset action
def get_processed_dataset
lines = Line.datatable_filter(params, params)
lines_filtered = lines.count
lines = lines.datatable_order(params.to_i,
params)
render json: { lines: lines,
draw: params.to_i,
recordsTotal: Line.count,
recordsFiltered: lines_filtered }
end
And add datatable_order class method to app/models/line.rb so that the file looks like
class Line < ApplicationRecord
DATATABLE_COLUMNS = %w.freeze
class << self
def datatable_filter(search_value, search_columns)
return all if search_value.blank?
result = none
search_columns.each do |key, value|
filter = where("#{DATATABLE_COLUMNS} ILIKE ?", "%#{search_value}%")
result = result.or(filter) if value
end
result
end
def datatable_order(order_column_index, order_dir)
order("#{Line::DATATABLE_COLUMNS} #{order_dir}")
end
end
end
We get column index and ordering direction from params and pass them to datatable_order method which takes the column by passed index and then apply ordering by that column and the direction to our previously queried lines. As you can see column index and ordering direction both come within a nested hash under “0” key. That means that there can be more than one hash like this i.e. more than one ordering condition. Though I won’t cover this use case and make the tutorial more complicated, I’d recommend you to work it out by yourself.
Refresh the page and see how the ordering got back in place and can even be changed now by clicking on column headers as it was before. One major difference is it’s the application backend who’s in charge of this work now.
Ok, there’s one more thing we still need to do and it’s the easiest part – pagination. Instead of implementing pagination by ourselves we’re gonna pass the buck to well-known gem Kaminari. So add gem 'kaminari' to your Gemfile, run bundle install and restart Rails server. Great, now you can update get_processed_dataset action with some pagination code.
def get_processed_dataset
lines = Line.datatable_filter(params, params)
lines_filtered = lines.count
lines = lines.datatable_order(params.to_i,
params)
lines = lines.page(params.to_i + 1).per(params)
render json: { lines: lines,
draw: params.to_i,
recordsTotal: Line.count,
recordsFiltered: lines_filtered }
end
We used length and start values from params which stand respectively for records per page number and the number of the record we need to start with. Then we apply them to the lines we queried and ordered before so that the action returns only the rows that were requested for current page by DataTables. Notice that we had to increment start number because Kaminari’s first-page number is 1 rather than 0 as it is in DataTables. Also, remember to get count of lines before you perform pagination and only 10 (or 25 or whatever) records left in lines variable.
For you could comfortably compare the Client-side processed table with the Server-side processed one I changed markup in app/views/pages/home.html.erb to this
<div class="container table-container">
<div class="row">
<div class="col-md-6">
<table id="ajax-table" class="display">
</table>
</div>
<div class="col-md-6">
<table id="server-side-table" class="display">
</table>
</div>
</div>
</div>
<div class="container table-container">
<div class="row">
<div class="col-md-8 col-md-offset-2">
<table id="static-table" class="display">
<thead>
<tr>
<th>Column1</th>
<th>Column2</th>
<th>Column3</th>
</tr>
</thead>
<tbody>
<tr>
<td>Data11</td>
<td>Data12</td>
<td>Data13</td>
</tr>
<tr>
<td>Data21</td>
<td>Data22</td>
<td>Data23</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
The tables are almost identical. The searching and pagination work with the same results.
The ordering is similar too but there’s one minor distinction. When the tables are ordered by some column e.g. Character and there are multiple rows with the same value in this column, the order within those row groups may vary from time to time because we didn’t do any work to handle this, at least on backend. This case is subtle and, as usual, I encourage you to experiment with it. What else you could experiment with, is using really large dataset with at least 1 million records and comparing the speed of Client-side processing and Server-side processing implemented on backend. Notice that “ILIKE” queries are not the fastest ones, so you may need to use some solution for full-text search, for example, this gem or even something big like ElasticSearch and Sphinx.
Conclusion
That’s it. We’ve set up the table with Server-side processing. In the first part, I told you about DataTables plug-in as a solution which could allow you not to hold an extra code for pagination, ordering and searching in your controllers but today we wrote this exactly extra code in the controller. To be honest that’s OK because you’ll never need to do this unless you deal with really huge tables in your app. But even if you do, it’s still nice to have one single tool to manage all 25 tables in the application. I mean even if you have to implement things on backend for one of your tables which is huge, there are still 24 more tables which DataTables will handle for you.
Thank you for going through this tutorial with me, hope it was helpful and you like it! But as usual, there is always much more info in official docs so if you decided to use DataTables in your projects https://datatables.net/ is at your service.
Author: Vadim Tsvid, Syndicode Ruby on Rails developer
