Editable Datagrid for Ruby on Rails Built with dhtmlxGrid

| Leave a comment

NOTE: This tutorial is for RoR 2. If you’re using RoR 3, please see additional details here.

We’ve also posted an updated tutorial for Ruby on Rails 4.

This tutorial will show you how to display tabular data in Ruby on Rails with grid js library, dhtmlxGrid Standard Edition, which is available under GNU GPL. At the end, we will have a datagrid with in-line editing, sorting, and filtering, which loads its data from the server and saves it back to the database using Ruby on Rails.

To illustrate, we will create a table of users. This is a very common task for nearly any web application. Working with RoR, we could use scaffolds, but they don’t work so well for navigation on large sets of data. Besides, dhtmlxGrid has client-side editing and filtering functionality, which can greatly simplify things. This is how our final grid will look:

dhtmlxGrid and RoR Example: Editable Ajax Grid

dhtmlxGrid and RoR Example: Editable Ajax Grid


Setting the Environment

To start, we create DB migration for a table of users. It will have 3 fields for storing First Name, Last Name and Phone Number. We will also create a couple of test records to see how the grid will look.

ruby script/generate migration create_users

class CreateUsers < ActiveRecord::Migration
  def self.up
    create_table :users do |t|
            t.string :first_name
            t.string :last_name
            t.string :phone
           
      t.timestamps
    end
    User.create(
        :first_name => "John",
        :last_name => "Smit",
        :phone => "555 198 877")
    User.create(
        :first_name => "Stanislav",
        :last_name => "Wolski",
        :phone => "456 456 454")    
  end

  def self.down
    drop_table :users
  end
end

rake db:migrate

Additionally we need to create a model and controller for our demo:

ruby script/generate model user
ruby script/generate controller admin

Now we are ready to start building the main part of the users table.

Loading Empty Grid

As I said, on the client side we will be using dhtmlxGrid.

When you download the grid package, unzip it and copy the dhtmlxGrid/codebase and dhtmlxDataProcessor/codebase folders to the /public/javascript/ folder of your demo application. The dhtmlxGrid package contains lots of examples and supporting materials which are not needed in the application, so we are taking only the code we need.

After that we can add our first action in /app/controllers/admin_controller.rb:

class AdminController < ApplicationController
    def view
    end
end

We also create the first view – the file /app/views/admin/view.rhtml:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <script src="/javascripts/codebase/dhtmlxcommon.js" type="text/javascript" charset="utf-8"></script>
        <script src="/javascripts/codebase/dhtmlxgrid.js" type="text/javascript" charset="utf-8"></script>
        <script src="/javascripts/codebase/dhtmlxgridcell.js" type="text/javascript" charset="utf-8"></script>
       
        <link rel="stylesheet" href="/javascripts/codebase/dhtmlxgrid.css" type="text/css" media="screen" charset="utf-8">
        <link rel="stylesheet" href="/javascripts/codebase/skins/dhtmlxgrid_dhx_skyblue.css" type="text/css" media="screen" charset="utf-8">
    </head>
    <body>
        <div id="grid_here" style="width:600px; height:400px;">
        </div>
        <script type="text/javascript" charset="utf-8">
            var grid = new dhtmlXGridObject("grid_here");
            grid.setImagePath("/javascripts/codebase/imgs/");
            grid.setHeader("First name, Last name, Phone");
            grid.setInitWidths("100,100,*");
            grid.setSkin("dhx_skyblue");
            grid.init();
        </script>
        <input type="button" value="Add" onclick="grid.addRow(grid.uid(),'new user')">
        <input type="button" value="Delete" onclick="grid.deleteSelectedRows()">
    </body>
</html>

As you can see, this view doesn’t contain any active logic, it just loads js and css files and initializes JavaScript grid on the page.

Some important points to note:

  • setImagePath points to codebase/imgs from the dhtmlxGrid package.
  • setHeader defines the structure of the grid, so the client-side grid will have columns to show data from our Users table (columns First Name, Last Name, and Phone Number).
  • setInitWidths command defines the widths of columns, and * as the width for the last column enables auto-size for this column.

 
Add new route in the routes.rb:

map.resource :admin,
    :collection => {
      :view       => :get,
      :dbaction => :get,
      :data        => :get
    }

Now, if you go to http://localhost:3000/admin/view, you will see something similar to the following:

dhtmlxGrid and RoR Example: Empty Grid

dhtmlxGrid and RoR Example: Empty Grid

This will be the structure of our table of users.

Filling Grid with Data

dhtmlxGrid can load its content from an XML data source, so loading data into the grid is pretty simple due to the ability of Rails to generate XML feeds.

We will add one more action in /app/controller/admin_controller.rb:

class AdminController < ApplicationController
    def view
    end
    def data
        @users = User.all()
    end
end

And also let’s create one more view. The file is /app/views/admin/data.rxml:

xml.instruct! :xml, :version=>"1.0"

xml.tag!("rows") do
    @users.each do |user|
        xml.tag!("row",{ "id" => user.id }) do
            xml.tag!("cell", user.first_name)
            xml.tag!("cell", user.last_name)
            xml.tag!("cell", user.phone)
        end
    end
end

The template has a .rxml extension because we will generate XML, not HTML. Inside the template we are outputting data from the Users table as XML.

Our last step will be to add one more line to the code in the /app/views/admin/view.rhtml file:

        <script type="text/javascript" charset="utf-8">
            var grid = new dhtmlXGridObject("grid_here");
            grid.setImagePath("/javascripts/codebase/imgs/");
            grid.setHeader("First name, Last name, Phone");
            grid.setInitWidths("100,100,*");
            grid.setSkin("dhx_skyblue");
            grid.init();
            grid.load("/admin/data"); //added !
        </script>

With this additional line, we’ve defined that the grid will load data from the XML feed we’ve just created.

Now our page at http://localhost:3000/admin/view is showing that the grid correctly loaded the initial set of data.

dhtmlxGrid and RoR Example: Load Records in Grid

dhtmlxGrid and RoR Example: Load Records in Grid

Saving Grid Data

Showing data in the grid was pretty easy, but it is useless without the ability to edit and save changes done by users when they edit grid records in the browser.

Fortunately, dhtmlxGrid has a special extension, DataProcessor, which is available in the grid package and allows you to synchronize client-side data changes with the server-side database. To “switch on” the DataProcessor extension, we need to implement one more action. In /app/controllers/admin_controller.rb:

class AdminController < ApplicationController
    def view
    end
    def data
        @users = User.all()
    end
    def dbaction
        #called for all db actions
        first_name = params["c0"]
        last_name    = params["c1"]
        phone            = params["c2"]
       
        @mode = params["!nativeeditor_status"]
       
        @id = params["gr_id"]
        case @mode
            when "inserted"
                user = User.new
                user.first_name = first_name
                user.last_name = last_name
                user.phone = phone
                user.save!
               
                @tid = user.id
            when "deleted"
                user=User.find(@id)
                user.destroy
               
                @tid = @id
            when "updated"
                user=User.find(@id)
                user.first_name = first_name
                user.last_name = last_name
                user.phone = phone
                user.save!
               
                @tid = @id
        end
    end
end

We’ve got quite a big piece of code compared to our previous steps.

In our new dbaction method, we are doing the following:

  • Getting parameters from the incoming request. We are using c0, c1 … cN parameters, which are related to the columns of grid: c0 – the first column, c1 – the second column, etc.
  • Getting the type of operation: inserted, updated or deleted
  • Getting the ID of a grid row

 
When we have all the above info, the code executes logic for each operation: it adds a new user for “inserted”; it deletes and updates user info for “deleted” and “inserted” operations.

In addition to this action we need to create one more XML view, which will be a response to an update operation. In the file /app/views/admin/dbaction.rxml:

xml.instruct! :xml, :version=>"1.0"

xml.tag!("data") do
    xml.tag!("action",{ "type" => @mode, "sid" => @id, "tid" => @tid })
end

The “tid” parameter in the above code provides a new ID value, which can (and will) be changed after the insert operation. The grid creates a temporary ID for a new record, which needs to be changed with an actual ID after saving the grid data.

The server-side part is ready, so we only need to point our grid to the dbaction feed.
In /app/views/admin/view.rhtml we add:

<script src="/javascripts/codebase/dhtmlxdataprocessor.js" type="text/javascript" charset="utf-8"></script>
        ...
        <script type="text/javascript" charset="utf-8">
            var grid = new dhtmlXGridObject("grid_here");
            grid.setImagePath("/javascripts/codebase/imgs/");
            grid.setHeader("First name, Last name, Phone");
            grid.setInitWidths("100,100,*");
            grid.setSkin("dhx_skyblue");
            grid.init();
            grid.load("/admin/data");
           
            dp = new dataProcessor("/admin/dbaction/");
            dp.init(grid);
        </script>
        <input type="button" value="Add" onclick="grid.addRow(grid.uid(),'new user')">
        <input type="button" value="Delete" onclick="grid.deleteSelectedRows()">

With this code, we are:

  • Including one more file, to activate DataProcessor
  • Adding two JS lines to initialize DataProcessor, pointing it to the dbaction feed
  • Adding buttons to add and delete rows in the grid

 
Now, if you try the application on http://localhost:3000/admin/view, you will see that all changes, as well as added and deleted records, are stored in the DB automatically.

The way to make “inserted” function to work is:
1. Click button. A new row with some default value will display in the bottom of the grid table.
2. Input real values in the cells of the new row in the grid table.
The new row will be saved in to database table. Refresh the page, you will see all the changes you just made.

Extra Functionality

If you remember, we were going to create a sortable and filterable datagrid. This functionality can be achieved without changes in server-side logic. We enable client-side filtering and sorting by adding the next code to the /app/views/admin/view.rhtml file:

<script src="/javascripts/codebase/ext/dhtmlxgrid_filter.js" type="text/javascript" charset="utf-8"></script>
        ...
        <script type="text/javascript" charset="utf-8">
            var grid = new dhtmlXGridObject("grid_here");
            grid.setImagePath("/javascripts/codebase/imgs/");
            grid.setHeader("First name, Last name, Phone");
            grid.attachHeader("#text_filter,#text_filter,#text_filter"); //added !
            grid.setColSorting("str,str,str");  //added !

Finally, we have a datagrid which uses client-side logic to perform sorting and filtering of data, and manages data communication with a Ruby on Rails backend.

dhtmlxGrid and RoR Example: Editable Grid

dhtmlxGrid and RoR Example: Editable Grid

By clicking on the header you can sort the grid by any column. Typing text in input fields in the header will filter the grid records by entered text. When you edit records in the grid, the changes will be saved in the database automatically, as well as row addition and deletion (use the buttons below the grid).

The full source code of the resulting demo can be downloaded here.

Modifications for Ruby on Rails 3 Integration

1. In routes.rb:

resource :admin do
    collection do
      get 'view'
      get 'data'
      get 'dbaction'
    end
  end

2. Rename files:

  • data.rxml to data.xml.builder
  • dbaction.rxml to dbaction.xml.builder

 
3. Replace:

grid.load(/login/data”);

with:

grid.load(/login/data.xml);

4. Replace:

dp = new dataProcessor(/login/dbaction/);

with:

dp = new dataProcessor(/login/dbaction.xml);

 
Settings for Rails 3.1

If you want to use dhtmlxGrid with Rails 3.1 with asset pipeline feature, here are the additional details:

1. Make sure that asset pipeline feature is enabled in config:

config/application.rb:

# Enable the asset pipeline
    config.assets.enabled = true

2. Copy all files from ‘dhtmlxgrid/codebase’ to the corresponding folders of Rails application in the directory ‘vendor/assets’:

  • js files – copy in ‘vendor/assets/javascripts/dhtmlx’
  • css files – copy in ‘vendor/assets/stylesheets/dhtmlx’
  • images – copy in ‘vendor/assets/images/dhtmlx’

 
3. Change the path to images in grid:

grid.setImagePath("/images/dhtmlx/imgs/");

Here is the code sample for adding dhtmlxGrid files on a page:

        <%= javascript_include_tag 'dhtmlx/dhtmlxcommon.js' %>
        <%= javascript_include_tag 'dhtmlx/dhtmlxgrid.js' %>
        <%= javascript_include_tag 'dhtmlx/dhtmlxgridcell.js' %>
        <%= javascript_include_tag 'dhtmlx/dhtmlxdataprocessor.js' %>

        <%= stylesheet_link_tag 'dhtmlx/dhtmlxgrid.css' %>
        <%= stylesheet_link_tag 'dhtmlx/skins/dhtmlxgrid_dhx_skyblue.css' %>

The image below shows an example of directory tree for dhtmlxGrid, when you use Rails 3.1 with assets pipeline feature.

dhtmlxGrid Files - Directory Tree

dhtmlxGrid Files - Directory Tree

Posted by Stas Wolski