Add jQuery datagrids to your Rails applications


Update : added support for subgrids. Have a look at the last example!

The jQuery grid plugin is an amazing Javascript project providing multi-functions Ajax datagrids for your web applications. With the 2dcJqgrid Rails plugin, you can now add these datagrids to your Ruby on Rails applications with just a few lines of code.

If you don't like the look & feel of this demo, you can easily customize it using jQuery themes.

Communications between your grids and the server will use the JSON format to exchange data.

The source code of this demo application is available on GitHub.

This solution is compatible with most of web browsers (even Internet Explorer 6 !!).

If you have any comments or suggestions, please post them here.



Installation


Inside your Rails application :

$ ./script/plugin install git://github.com/ahe/2dc_jqgrid.git
$ ./script/plugin install git://github.com/thoughtbot/squirrel.git

./script/generate scaffold user pseudo:string firstname:string lastname:string email:string role:string

Add some data to your migration file, you can use this one.
Run the migration :
$ rake db:migrate

Open the default layout created for you by the scaffold script (layouts/users.html.erb) and add the required JS & CSS in the header :
<%= jqgrid_stylesheets %>
<%= jqgrid_javascripts %>

Replace the index method in your controller by this one : 
def index
  users = User.find(:all) do
    if params[:_search] == "true"
      pseudo    =~ "%#{params[:pseudo]}%" if params[:pseudo].present?
      firstname =~ "%#{params[:firstname]}%" if params[:firstname].present?
      lastname  =~ "%#{params[:lastname]}%" if params[:lastname].present?                
      email     =~ "%#{params[:email]}%" if params[:email].present?
      role      =~ "%#{params[:role]}%" if params[:role].present?        
    end
    paginate :page => params[:page], :per_page => params[:rows]      
    order_by "#{params[:sidx]} #{params[:sord]}"
  end

  respond_to do |format|
    format.html
    format.json { render :json => users.to_jqgrid_json([:id,:pseudo,:firstname,:lastname,:email,:role], 
                                                       params[:page], params[:rows], users.total_entries) }
  end
end

The query has been added into the controller for clarity purposes in this demo. It's of course a better idea to create a method in your User class. Notice how the squirrel plugin makes it easy to add filters and pagination to our finder.

Also notice the to_jqgrid_json method, it will generate the required JSON for you. The order of the fields in the first parameter matters, it should be the same than the display order in your datagrid.


You are now ready to create datagrids.


Simple DataGrid with search, pagination & sorting




The code used to create this grid is : 
<%= jqgrid("Football Players", "players", "/users",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "pseudo", :label => "Pseudo" },
		{ :field => "firstname", :label => "Firstname" },
		{ :field => "lastname", :label => "Lastname" },
		{ :field => "email", :label => "Email" },
		{ :field => "role", :label => "Role" }				
	]
) %>

The first argument of the jqgrid helper is the title of your grid.
The second one is his DOM ID.
The third one is the URL used to retrieve data.
Finally, it takes an array of hashes to configure columns.



Simple DataGrid with selection link/button


Get ID of selected row

In this case, we added a "Get ID of selected row" link. When this link is clicked, the Javascript method defined by :selection_handler is called, with the ID of the row as a parameter : 
<script type="text/javascript">
function handleSelection(id) {
	alert('ID selected : ' + id);
}
</script>
<%=jqgrid("Football Players", "players_2", "/users",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "pseudo", :label => "Pseudo" },
		{ :field => "firstname", :label => "Firstname" },
		{ :field => "lastname", :label => "Lastname" },
		{ :field => "email", :label => "Email" },
		{ :field => "role", :label => "Role" }
	],
	{ :selection_handler => "handleSelection" }
)%>
<a href="#" id="players_2_select_button">Get ID of selected row</a>

The ID of this link is very important, it must be the ID of the jqgrid + "_select_button". You can use a button instead of a link if you want.



Simple DataGrid with direct selection




If you want to call the handler directly when you select a row instead of clicking on a link/button, use the following options. Of course, you also need the Javascript method "handleSelection" defined in the previous section. 
<%=jqgrid("Football Players", "players_3", "/users",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "pseudo", :label => "Pseudo" },
		{ :field => "firstname", :label => "Firstname" },
		{ :field => "lastname", :label => "Lastname" },
		{ :field => "email", :label => "Email" },
		{ :field => "role", :label => "Role" }
	],
	{ :selection_handler => "handleSelection", :direct_selection => true }
)%>



Simple DataGrid with multiple selections


Get IDs of selected rows 

>%=jqgrid("Football Players", "players_4", "/users",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "pseudo", :label => "Pseudo" },
		{ :field => "firstname", :label => "Firstname" },
		{ :field => "lastname", :label => "Lastname" },
		{ :field => "email", :label => "Email" },
		{ :field => "role", :label => "Role" }
	],
	{ :selection_handler => "handleSelection", :multi_selection => true }
)%>
<a href="#" id="players_4_select_button">Get IDs of selected rows</a>



Simple DataGrid with master details





We need associated data to create this master-details grid :
$ ./script/generate model pet name:string user_id:integer

Add a relationship in your User (has_many :pets) and Pet (belongs_to :user) models.
Then add data to your migration file : you can use this one.

Create the table :
$ rake db:migrate

And add the pets method in your users controller : 
def pets
  if params[:id].present?
    pets = User.find(params[:id]).pets.find(:all) do
      paginate :page => params[:page], :per_page => params[:rows]      
      order_by "#{params[:sidx]} #{params[:sord]}"        
    end
    total_entries = pets.total_entries
  else
    pets = []
    total_entries = 0
  end
  respond_to do |format|
    format.json { render :json => pets.to_jqgrid_json([:id,:name], params[:page], params[:rows], total_entries) }
  end
end

Don't forget to edit your routes and restart the server :
map.resources :users, :collection => { :pets => :get }

You can finally add your grids : 
<%=jqgrid("Football Players", "players_5", "/users",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "pseudo", :label => "Pseudo" },
		{ :field => "firstname", :label => "Firstname" },
		{ :field => "lastname", :label => "Lastname" },
		{ :field => "email", :label => "Email" },
		{ :field => "role", :label => "Role" }
	],
	{ :master_details => true, :details_url => "/users/pets", :details_caption => "Pets" }
)%>
<br/>
<%=jqgrid("Pets", "players_5_details", "/users/pets",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "name", :label => "Name", :width => 500, :align => 'center' }
	]
)%>

The DOM ID of your details grid is important, it must be the ID of the master grid + "_details".


For evident reasons, data manipulation has been disabled in this demo

Data manipulation with inline editing




We need one last method in our controller to handle data manipulation.
Create the post_data method in your users controller : 
def post_data
  if params[:oper] == "del"
    User.find(params[:id]).destroy
  else
    user_params = { :pseudo => params[:pseudo], :firstname => params[:firstname], :lastname => params[:lastname], 
                    :email => params[:email], :role => params[:role] }
    if params[:id] == "_empty"
      User.create(user_params)
    else
      User.find(params[:id]).update_attributes(user_params)
    end
  end
  render :nothing => true
end

It's of course your role to add security & validation rules.

If protect_from_forgery is on, disable it for this action :
protect_from_forgery :except => [:post_data]

Edit your routes and restart the server :
map.resources :users, :collection => { :pets => :get, :post_data => :post }

You can now add the grid : 
<%=jqgrid("Football Players", "players_6", "/users",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "pseudo", :label => "Pseudo", :editable => true },
		{ :field => "firstname", :label => "Firstname", :editable => true },
		{ :field => "lastname", :label => "Lastname", :editable => true },
		{ :field => "email", :label => "Email", :editable => true },
		{ :field => "role", :label => "Role", :editable => true }
	],
	{ :add => true, :edit => true, :inline_edit => true, :delete => true, :edit_url => "/users/post_data" }
)%>



Data manipulation with modal editing (+ navigation)



<%=jqgrid("Football Players", "players_7", "/users",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "pseudo", :label => "Pseudo", :editable => true },
		{ :field => "firstname", :label => "Firstname", :editable => true },
		{ :field => "lastname", :label => "Lastname", :editable => true },
		{ :field => "email", :label => "Email", :editable => true },
		{ :field => "role", :label => "Role", :editable => true }
	],
	{ :add => true, :edit => true, :inline_edit => false, :delete => true, :edit_url => "/users/post_data" }
)%>



Data manipulation with various input types



<%=jqgrid("Football Players", "players_8", "/users",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "pseudo", :label => "Pseudo", :editable => true },
		{ :field => "firstname", :label => "Firstname", :editable => true, :edittype => "checkbox",
		  :editoptions => { :value => "Yes:No" } },
		{ :field => "lastname", :label => "Lastname", :editable => true },
		{ :field => "email", :label => "Email", :editable => true, :edittype => "textarea", :editoptions => { :rows => 3, :cols => 30 } },
		{ :field => "role", :label => "Role", :editable => true, :edittype => "select",
		  :editoptions => { :value => [["admin","admin"], ["player", "player"], ["defender","defender"]] } }
	],
	{ :add => true, :edit => true, :inline_edit => true, :delete => true, :edit_url => "/users/post_data" }
)%>

You can add values for a select box more easily using collections :
:editoptions => { :data => [Category.all, :id, :title] }



Subgrids



<%=
jqgrid("Football Players", "players_9", "/users",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "pseudo", :label => "Pseudo" },
		{ :field => "firstname", :label => "Firstname" },
		{ :field => "lastname", :label => "Lastname" },
		{ :field => "email", :label => "Email" },
		{ :field => "role", :label => "Role" }
	],
	{ 
		:subgrid => { :url => "/users/pets",
					  :columns => [
						{ :field => "id", :label => "ID", :width => 35, :resizable => false },
						{ :field => "name", :label => "Name" }
					  ]
					}
	}
)%>

Search is disabled by default, you can add it using :search => true. Datagrids options are available here too (rows_per_page, sort_column, ...).



Columns options


You can add the following options to each column :

Attribute Values
width Size of the cell
align 'left', 'right', 'center'
sortable true / false
resizable true / false
editable true / false



Datagrid options


You can the following options to your datagrid : 
<%=jqgrid("Football Players", "players", "/users",
	[
		{ :field => "id", :label => "ID", :width => 35, :resizable => false },
		{ :field => "pseudo", :label => "Pseudo" },
		{ :field => "firstname", :label => "Firstname" },
		{ :field => "lastname", :label => "Lastname" },
		{ :field => "email", :label => "Email" },
		{ :field => "role", :label => "Role" }
	],
	{ :rows_per_page => 30 }
)%>

Attribute Values
rows_per_age Number of rows per page
sort_column Default sort column
sort_order Default sort order 'asc' / 'desc'
grid_loaded 'javaScriptMethod', the javascript method specified will be called once data are loaded into the grid.