DataTables is a great tool which you can use to easily manipulate with tables in your web apps by adding pagination, sorting and searching through JavaScript. But if you need to manipulate with a table which has thousands of records you need to use server side processing and that can be a little difficult to accomplish in the right way. There is a great railscast episode about this but it's out of date since DataTables changed a lot since then. There is also a gem that you can use to solve this problem but it doesn't support Oracle and I had a hard time configuring it in a way that I needed to, so I decided to do it by myself from scratch but based on code provided in Ryan Bytes' episode.

A PROBLEM

Let's say you need to create an index page with products listed in a table with search bar on top and you need to use submitted search parameters to search for records in your database by product code and product name. Btw, I used Spawner Data Generator to generate enough records so I can test server side processing properly.

General information

For this purpose we will be using rails 4 with HAML, CoffeScript, Zurb Foundation as front-end framework, kaminari for pagination and Font Awesome for icons, so you need to configure your gemfile to include all of these. Don't forget to run 'bundle install':

gem'jquery-rails'# Use jquery as the JavaScript librarygem'jquery-ui-rails'# JQuery UI Librarygem'jquery-datatables-rails'# Use datatables as default tables in the applicationgem'font-awesome-rails'# Icons

Models

Let's assume we have Category model (code, name) and Product model (code, name, description, category), so Product model has one 'belongs_to' association and we need to put category name in product table on our index page. I won't post code that represents these models since there would be too much code in this post and it is very easy to code this.

INDEX page, CONTROLLER, ROUTES, JAVASCRIPT

Now let's get to the point... In your index page you need to wrap the header row inside a thead element and the body section in a tbody element. Nothing more than that since you are going to render records after you get them from the server. I added one additional column here - ID of the record which we won't display but it is fun to add more tricks to the code and you can later use it to select a row from a table and do something with it. Additionally, there is a search field where user can enter some text to search by product code or name. Later we will see how we are going to use this.

Another thing we need to do in this step is to add some code to the controller and to configure routes:

classProductsController<ApplicationController

defindex

end

defdatatable_ajaxrenderjson:ProductsDatatable.new(view_context)end

Test::Application.routes.drawdo

resources:products

get'products_ajax/datatable_ajax',to:'products#datatable_ajax'

end

As you can see, the index action is empty. I didn't want to put code for fetching records for DataTables here since index action simply isn't for that. In index action you want to do something with the page in general and data in the page, for example fetch some other data besides data for the table. For that reason, we created separate action just for that purpose and that action will return just json, nothing else. The last thing in this step is to create products.js.coffee file where we can tell DataTables all information about the table and server side processing:

You can arrange your columns as you wish, I did it in this way. There is a class name for the first column since we don't want to display column with ID, so you need to add some CSS in here, for example in products.css.scss file. 'row_config' class name tells to each table cell that if content in that cell is too long, the content won't be broken in additional lines - the content will be displayed in that cell as is, with dots (...) if content is longer. In that way you keep your table well formatted.

One more thing - we will send additional parameters via DataTable using this 'data' part, You can send as much additional parameters as you like. So, when you perform some action on the table, DataTables will collect other information from the page and send them to the url you defined.

AJAX PART

And finally, here is the main part of the application. In this section we will perform everything needed to fetch, prepare and display data in a proper way:

defsort_order_filterrecords=my_search.order("#{sort_column} #{sort_direction}")ifparams[:search][:value].present?records=records.where("PRODUCTS.CODE like :search or lower(PRODUCTS.NAME) like :search",search:"%#{params[:search][:value]}%")endrecordsend

This file is too long to explain every single detail, but you can use railscast episode as a guide. Also, you can notice that you need to define scopes in your Product model class and use it to filter data according to the parameters submitted by the user. The scope can be defined in this way for example:

scope:filter_product_name,->(product_name){where("lower(PRODUCTS.NAME) like :search",search:"%#{product_name.downcase}%")}

You maybe noticed that I use LOWER function a lot. It's Oracle's function you can use to down-case your string, so search can be performed regardless of the way user entered search parameter. You can also use this when you specify sort columns as shown above. I am not sure if you can customized your searches and sorts in this way using gem I mentioned in the beginning.

THE END

I hope this helps a little. It's pretty easy to configure your table with DataTables if you know the basics, but for beginners it can be really hard to collect all the pieces and make a functional solution.

Oracle gems

First you need to install few necessary gems, so put this code in your GEMFILE:

database.yml

Next, you need to edit this file, something like this:

This is a little bit different than configuring your postgres database since you define here a user that will be used as a database name, so you don't need to enter a database name separately, you need to put your SID under 'database' entry.

Oracle user

Since you configured your database.yml file like this, you need to manually create new user in your oracle database and that user will be 'testing'. Go to SQLPlus, log in as an admin and type this:

You created your user and now you need to grant him some privileges:

THE END

Now you do all that 'rake db:migrate' shit but there's no need to do 'rake db:create' too, since this is ORACLE, and ORACLE has to be different and special :)