Introduction
This tutorial will guide you on how to display tabular data in Ruby on Rails environment within editable datagrid. 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:
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 download the latest package from dhtmlx.com.
When you downloaded the grid package, unzip it and copy dhtmlxGrid/codebase and dhtmlxDataProcessor/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:
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");
</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.
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” 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 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 for inserted, 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 insert operation. 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");
grid.setColSorting("str,str,str");
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.
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).
The full source code of the resulting demo can be downloaded here.
