Sortable and Editable Datagrid for Ruby on Rails

Introduction

This tutorial will guide you on how to display tabular data in Ruby on Rails environment within editabledatagrid. We will use dhtmlxGrid, an open-source JavaScript grid control, as a base of our table. As a result, we will end up with a datagrid with in-line editing, sorting, and filtering, which loads its data from server and saves it back to the database using Ruby on Rails.

As an example, we will create a table of users. This is quite a common task nearly for any web application. Working with RoR, we could use scaffolds but they are not so good for navigation on large sets of data. Besides,dhtmlxGrid has client-side editing and filtering functionality, which can simplify things a lot. This is how our final grid will look like:

final 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 like.

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 have all prepared to start building the main part of the Users table.

Loading Empty Grid

As I’ve said, on the client side, we will be using dhtmlxGrid, which is available under GNU GPL. You can downloadthe latest package from dhtmlx.com.

When you downloaded the grid package, unzip it and copy dhtmlxGrid/codebase anddhtmlxDataProcessor/codebase folders to the /public/javascript/ folder of our application. dhtmlxGrid package contains a lot of samples and supporting materials, which are not needed in the application, so we are taking only necessary code parts.

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="http://yoursite.com/javascripts/codebase/dhtmlxcommon.js"
		type="text/javascript" charset="utf-8"></script>
        <script src="http://yoursite.com/javascripts/codebase/dhtmlxgrid.js" 
		type="text/javascript" charset="utf-8"></script>
        <script src="http://yoursite.com/javascripts/codebase/dhtmlxgridcell.js"
		type="text/javascript" charset="utf-8"></script>
        
        <link rel="stylesheet" 
	href="http://www.codeproject.com/javascripts/codebase/dhtmlxgrid.css" 
	type="text/css" media="screen" charset="utf-8">
        <link rel="stylesheet" href="http://www.codeproject.com/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 I would like to mention:

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

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

empty grid

This will be the structure of our table of users.

Filling Grid with Data

dhtmlxGrid can load its content from XML datasource, 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 .rxml extension, because we will generate XML, not HTML. Inside the template, we are outputting data from Users table as XML.

Our last step will be to add one more line to the code of /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 defined that the grid will load data from XML feed we’ve just created.

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

grid with some test records

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 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 server-side database. To “switch on” DataProcessorextension, 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 comparing our previous steps.

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

  • Getting parameters from 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, and etc.)
  • Getting type of operation: inserted, updated or deleted
  • Getting ID of a grid row

When we have all the above information, the code executes logic for each operation: adds a new user forinserted, deletes and updates user information for deleted and inserted operations.

In addition to this action, we need to create one more XML view, which will be a response for update operation. In 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 insertoperation. The grid creates a temporary ID for a new record, which needs to be changed with 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="http://yoursite.com/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"  önclick="grid.addRow(grid.uid(),'new user')">
<input type="button" value="Delete"  önclick="grid.deleteSelectedRows()">

By this code, we are:

  • including one more file, to activate DataProcessor
  • adding two JS lines to init 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 DB automatically.

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 will enable client-side filtering and sorting by adding the next code to the /app/views/admin/view.rhtml file:

<script src="http://yoursite.com/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’ve got a datagrid which uses client-side logic to perform sorting and filtering of data, and manages data communication with Ruby on Rails backend.

final 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 DB automatically, as well as row addition and deletion (use the buttons below the grid).

Source

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s