• Introduction
• Use jqGrid to Display a MySQL Table
• Get Started!
• Download jqGrid
• Download jQuery UI Theme
• Create MySQL Table
• Talk to the Browser with Two Views: Catalyst::View::TT, Catalyst::View::JSON
• Talk to jqGrid: Catalyst::TraitFor::Controller::jQuery::jqGrid
• Make a Simple 'edit'
• Improve 'edit' and Add 'add' and 'delete'
• Final Version
• Thanks
• About

Introduction

I am a Perl Newbie. I hope this tutorial is helpful to Newbies interested in using jqGrid with Catalyst.

From the jqGrid documentation:

http://www.trirand.com/jqgridwiki/doku.php

jqGrid is an Ajax-enabled JavaScript control that provides solutions for representing and manipulating tabular data on the web. Since the grid is a client-side solution loading data dynamically through Ajax callbacks, it can be integrated with any server-side technology, including PHP, ASP, Java Servlets, JSP, ColdFusion, and Perl.

jqGrid uses a jQuery JavaScript Library and is written as a plugin for that package. For more information on jQuery, please refer to the jQuery web site:

http://jquery.com

jqGrid's Home page can be found here:

http://www.trirand.com

Working examples of jqGrid, with explanations, can be found here:

http://www.trirand.com/blog/jqgrid/jqgrid.html

The last development version can be obtained from GitHub:

https://github.com/tonytomov/jqGrid

Use jqGrid to Display a MySQL Table

Get Started!

As simply as possible we will display a MySQL table with jqGrid. We first download jqGrid, then create a MySQL table. I'll provide a script to create and populate the table with data.

Download jqGrid

The following link will take you to the jqGrid homepage where you can select "Downloads". You may not want to for other projects, but for this one select and download all components:

http://www.trirand.com

I am using jqGrid version 4.4.1.

Download jQuery UI Theme

You can roll your own jQuery UI theme or select from a gallery. For this project select the Cupertino theme from the gallery and download all components:

http://jqueryui.com/themeroller

I am using jQuery-UI version 1.8.22.

Create MySQL Table

Use the following script to create your database for this tutorial:

myfirstgrid01.sql:

CREATE TABLE inventory (                                                     
  inv_id int(11) NOT NULL AUTO_INCREMENT,                                             
  client_id int(11) NOT NULL,                                                     
  amount decimal(10,2) NOT NULL DEFAULT '0.00',                                   
  tax decimal(10,2) NOT NULL DEFAULT '0.00',                                      
  total decimal(10,2) NOT NULL DEFAULT '0.00',                                    
  note char(100) DEFAULT NULL,                                 
  PRIMARY KEY  (inv_id) 
);
INSERT INTO inventory(
  inv_id,
  client_id,
  amount,
  tax,
  total,
  note)
VALUES
  (1,2339,103.98,45.34,149.32,"This is record 1."),
  (2,1171,104.98,46.34,151.32,"This is record 2."), 
  (3,5232,105.98,47.34,153.32,"This is record 3."), 
  (4,9110,106.98,48.34,155.32,"This is record 4."), 
  (5,5232,107.98,49.34,157.32,"This is record 5."), 
  (6,8471,108.98,50.34,159.32,"This is record 6."), 
  (7,2339,109.98,51.34,161.32,"This is record 7."), 
  (8,8471,110.98,52.34,163.32,"This is record 8."), 
  (9,3632,111.98,53.34,165.32,"This is record 9."), 
  (10,6483,112.98,54.34,167.32,"This is record 10."), 
  (11,2333,113.98,55.34,169.32,"This is record 11."), 
  (12,9110,114.98,56.34,171.32,"This is record 12."), 
  (13,1005,115.98,57.34,173.32,"This is record 13."), 
  (14,9987,116.98,58.34,175.32,"This is record 14."), 
  (15,6483,117.98,59.34,177.32,"This is record 15."), 
  (16,5249,118.98,60.34,179.32,"This is record 16."), 
  (17,2333,119.98,61.34,181.32,"This is record 17."), 
  (18,1171,120.98,62.34,183.32,"This is record 18."), 
  (19,1005,121.98,63.34,185.32,"This is record 19."), 
  (20,7377,122.98,64.34,187.32,"This is record 20."), 
  (21,1171,123.98,65.34,189.32,"This is record 21."), 
  (22,5232,124.98,66.34,191.32,"This is record 22."), 
  (23,9110,125.98,67.34,193.32,"This is record 23."), 
  (24,1728,126.98,68.34,195.32,"This is record 24."), 
  (25,4732,127.98,69.34,197.32,"This is record 25."), 
  (26,8221,128.98,70.34,199.32,"This is record 26."), 
  (27,9110,129.98,71.34,201.32,"This is record 27."), 
  (28,2232,130.98,72.34,203.32,"This is record 28."), 
  (29,6483,131.98,73.34,205.32,"This is record 29."), 
  (30,5232,132.98,74.34,207.32,"This is record 30."), 
  (31,9483,133.98,75.34,209.32,"This is record 31."), 
  (32,2985,134.98,76.34,211.32,"This is record 32."), 
  (33,8221,135.98,77.34,213.32,"This is record 33."), 
  (34,7377,136.98,78.34,215.32,"This is record 34."), 
  (35,8471,137.98,79.34,217.32,"This is record 35."), 
  (36,3632,138.98,80.34,219.32,"This is record 36."), 
  (37,2112,139.98,81.34,221.32,"This is record 37.");

Note: When I used it, the jqGrid tutorial provided a script with dates Perl tries to use as DateTime objects. There are two reasons why I chose to not use them: 1) I want my Perl code to be as simple as possible 2) MySQL wants dates to be formatted YY/MM/DD. The jqGrid tutorial's date is formatted MM/DD/YY—I didn't want this to be a problem to solve at this time.

These are the commands I used to create the database:

$ mysql -uusername -ppassword

mysql> create database myfirstgrid01; 

mysql> use myfirstgrid01; 

mysql> source myfirstgrid01.sql;

You can take a look at the database if you want to:

mysql> show tables;
+-------------------------+
| Tables_in_myfirstgrid01 |
+-------------------------+
| inventory               |
+-------------------------+
1 row in set (0.00 sec)

mysql> describe inventory;
+-----------+---------------+------+-----+---------+----------------+
| Field     | Type          | Null | Key | Default | Extra          |
+-----------+---------------+------+-----+---------+----------------+
| inv_id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| client_id | int(11)       | NO   |     | NULL    |                |
| amount    | decimal(10,2) | NO   |     | 0.00    |                |
| tax       | decimal(10,2) | NO   |     | 0.00    |                |
| total     | decimal(10,2) | NO   |     | 0.00    |                |
| note      | char(100)     | YES  |     | NULL    |                |
+-----------+---------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> select * from inventory;
+--------+-----------+--------+-------+--------+--------------------+
| inv_id | client_id | amount | tax   | total  | note               |
+--------+-----------+--------+-------+--------+--------------------+
|      1 |      2339 | 103.98 | 45.34 | 149.32 | This is record 1.  |
|      2 |      1171 | 104.98 | 46.34 | 151.32 | This is record 2.  |
|      3 |      5232 | 105.98 | 47.34 | 153.32 | This is record 3.  |
|      4 |      9110 | 106.98 | 48.34 | 155.32 | This is record 4.  |
|      5 |      5232 | 107.98 | 49.34 | 157.32 | This is record 5.  |
|      6 |      8471 | 108.98 | 50.34 | 159.32 | This is record 6.  |
|      7 |      2339 | 109.98 | 51.34 | 161.32 | This is record 7.  |
|      8 |      8471 | 110.98 | 52.34 | 163.32 | This is record 8.  |
|      9 |      3632 | 111.98 | 53.34 | 165.32 | This is record 9.  |
|     10 |      6483 | 112.98 | 54.34 | 167.32 | This is record 10. |
|     11 |      2333 | 113.98 | 55.34 | 169.32 | This is record 11. |
|     12 |      9110 | 114.98 | 56.34 | 171.32 | This is record 12. |
|     13 |      1005 | 115.98 | 57.34 | 173.32 | This is record 13. |
|     14 |      9987 | 116.98 | 58.34 | 175.32 | This is record 14. |
|     15 |      6483 | 117.98 | 59.34 | 177.32 | This is record 15. |
|     16 |      5249 | 118.98 | 60.34 | 179.32 | This is record 16. |
|     17 |      2333 | 119.98 | 61.34 | 181.32 | This is record 17. |
|     18 |      1171 | 120.98 | 62.34 | 183.32 | This is record 18. |
|     19 |      1005 | 121.98 | 63.34 | 185.32 | This is record 19. |
|     20 |      7377 | 122.98 | 64.34 | 187.32 | This is record 20. |
|     21 |      1171 | 123.98 | 65.34 | 189.32 | This is record 21. |
|     22 |      5232 | 124.98 | 66.34 | 191.32 | This is record 22. |
|     23 |      9110 | 125.98 | 67.34 | 193.32 | This is record 23. |
|     24 |      1728 | 126.98 | 68.34 | 195.32 | This is record 24. |
|     25 |      4732 | 127.98 | 69.34 | 197.32 | This is record 25. |
|     26 |      8221 | 128.98 | 70.34 | 199.32 | This is record 26. |
|     27 |      9110 | 129.98 | 71.34 | 201.32 | This is record 27. |
|     28 |      2232 | 130.98 | 72.34 | 203.32 | This is record 28. |
|     29 |      6483 | 131.98 | 73.34 | 205.32 | This is record 29. |
|     30 |      5232 | 132.98 | 74.34 | 207.32 | This is record 30. |
|     31 |      9483 | 133.98 | 75.34 | 209.32 | This is record 31. |
|     32 |      2985 | 134.98 | 76.34 | 211.32 | This is record 32. |
|     33 |      8221 | 135.98 | 77.34 | 213.32 | This is record 33. |
|     34 |      7377 | 136.98 | 78.34 | 215.32 | This is record 34. |
|     35 |      8471 | 137.98 | 79.34 | 217.32 | This is record 35. |
|     36 |      3632 | 138.98 | 80.34 | 219.32 | This is record 36. |
|     37 |      2112 | 139.98 | 81.34 | 221.32 | This is record 37. |
+--------+-----------+--------+-------+--------+--------------------+
37 rows in set (0.00 sec)

Talk to the Browser with Two Views: Catalyst::View::TT, Catalyst::View::JSON

Let's get started creating our Catalyst application:

$ catalyst.pl MyFirstGrid 
$ cd MyFirstGrid
$ perl Makefile.PL
$ script/myfirstgrid_create.pl view TT TT 
$ script/myfirstgrid_create.pl view JSON JSON 
$ script/myfirstgrid_create.pl model DB DBIC::Schema \
> MyFirstGrid::Schema::DB create=static \
> dbi:mysql:myfirstgrid01 username password

We need to specify which stash keys are exposed as a JSON response. I did so in the application class:

MyFirstGrid.pm:

__PACKAGE__->config({
  'View::JSON' => {
    expose_stash => qw(json_data)
  }
});

(See the Catalyst::View::JSON docs for details.)

Talk to jqGrid: Catalyst::TraitFor::Controller::jQuery::jqGrid

This is where we start making Catalyst magic happen.

Without Catalyst::TraitFor::Controller::jQuery::jqGrid we would need to calculate jqGrid parameters to find data corresponding to jqGrid pages, then find, prepare and send it to jqGrid for display in our browser. Catalyst::TraitFor::Controller::jQuery::jqGrid does most of this for us.

If I understand Catalyst correctly, Catalyst::TraitFor::Controller::jQuery::jqGrid is a Moose role, which we use in our controller like this:

BEGIN { extends 'Catalyst::Controller' }
with 'Catalyst::TraitFor::Controller::jQuery::jqGrid';

Make a Simple 'edit'

First we will install jqGrid into our Catalyst application:

'root/static/css' and 'root/static/js' contain jqGrid files:

MyFirstGrid application classes:

Once we get this far the remaining focus of our work will be with our template we will now create, our root controller, and using our Catalyst console and browser developer tools to watch Catalyst and jqGrid talk to each other.

Create the following template:

root/index.tt:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>My First Grid</title>

<link rel="stylesheet" type="text/css" media="screen" href="[% c.uri_for('/static/css/cupertino/jquery-ui-1.8.22.custom.css') %]" />
<link rel="stylesheet" type="text/css" media="screen" href="[% c.uri_for('/static/css/ui.jqgrid.css') %]" />

<style type="text/css">
html, body {
  margin: 0;
  padding: 0;
  font-size: 75%;
}
</style>

<script src="[% c.uri_for('/static/js/jquery-1.7.2.min.js') %]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/js/i18n/grid.locale-en.js') %]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/js/jquery.jqGrid.min.js') %]" type="text/javascript"></script>

<script type="text/javascript">
$(function(){ 
  $("#list").jqGrid({
    url:"[% c.uri_for("getdata") %]",
    datatype:'json',
    mtype:'GET',
    colNames:['Inv No', 'Client ID', 'Amount','Tax','Total','Notes'],
    colModel:[ 
      {name:'inv_id', index:'inv_id', width:55}, 
      {name:'client_id', index:'client_id', width:55},
      {name:'amount', index:'amount', width:80, align:'right'}, 
      {name:'tax', index:'tax', width:80, align:'right'}, 
      {name:'total', index:'total', width:80, align:'right'}, 
      {name:'note', index:'note', width:150, sortable:false} 
    ],
    pager:'#pager',
    rowNum:10,
    rowList:[10,20,30],
    sortname:'inv_id',
    sortorder:'desc',
    viewrecords:true,
    gridview:true,
    caption:'My First Grid'
  }); 
}); 
</script>
</head>

<body>
<table id="list"><tr><td/></tr></table> 
<div id="pager"></div> 
</body>
</html>

This template is only slightly modified from the jqGrid tutorial MyFirstGrid:

http://www.trirand.com/jqgridwiki/doku.php?id=wiki:first_grid

Consider the settings and options used in our code:

url	Tells us where to get the data. Typically this is a server-side function with a connection to a database which returns the appropriate information to be filled into the Body layer in the grid
datatype	This tells jqGrid the type of information being returned so it can construct the grid. In this case, we tell the grid that we expect XML data to be returned from the server, but other formats are possible. For a list of all available datatypes refer to API Methods
mtype	Tells us how to make the Ajax call: either 'GET' or 'POST'. In this case, we will use the GET method to retrieve data from the server
colNames	An array in which we place the names of the columns. This is the text that appears in the head of the grid (Header layer). The names are separated with commas
colModel	An array that describes the model of the columns. This is the most important part of the grid. Here I explain only the options used above. For the complete list of options see colModel API name: The name of the column. This name does not have to be the name from the database table, but later we will see how we can use this when we have different data formats. index: The name passed to the server on which to sort the data (note that we could pass column numbers instead). Typically this is the name (or names) from the database – this is server-side sorting, so what you pass depends on what your server expects to receive. width: The width of the column, in pixels. align: The alignment of the column. sortable: Specifies if the data in the grid can be sorted on this column; if false, clicking on the header has no effect.
pager	Defines that we want to use a pager bar to navigate through the records. This must be a valid HTML element; in our example we gave the div the id of “pager”, but any name is acceptable. Note that the Navigation layer (the “pager” div) can be positioned anywhere you want, determined by your HTML; in our example we specified that the pager will appear after the Body layer.
rowNum	Sets how many records we want to view in the grid. This parameter is passed to the URL for use by the server routine retrieving the data
rowList	An array to construct a select box element in the pager in which we can change the number of the visible rows. When changed during the execution, this parameter replaces the rowNum parameter that is passed to the url
sortname	Sets the initial sorting column. Can be a name or number. This parameter is added to the URL for use by the server routine
viewrecords	Defines whether we want to display the number of total records from the query in the pager bar
caption	Sets the caption for the grid. If this parameter is not set the Caption layer will be not visible

package MyFirstGrid::Controller::Root;
use Moose;
use namespace::autoclean;

BEGIN { extends 'Catalyst::Controller' }
with 'Catalyst::TraitFor::Controller::jQuery::jqGrid'; 

__PACKAGE__->config(namespace => '');

sub index :Path :Args(0) {
  my ($self, $c) = @_;

  $c->detach($c->view("TT"));
}

sub getdata :Local {
  my ($self, $c) = @_;

  my $inv_rs = $c->model('DB::Inventory')->search({});
  $inv_rs = $self->jqgrid_page($c, $inv_rs);
  my @row_data;
  while (my $inv = $inv_rs->next) {
    my $single_row = {
      cell => [
        $inv->inv_id,
        $inv->client_id,
        $inv->amount,
        $inv->tax,
        $inv->total,
        $inv->note,
      ],
    };
    push @row_data, $single_row;
  }
  $c->stash->{json_data}{rows} = \@row_data;
  $c->stash->{current_view} = 'JSON'; 
}

sub default :Path {
  my ($self, $c) = @_;

  $c->response->body('Page not found');
  $c->response->status(404);
}

# Attempt to render a view, if needed.

sub end :ActionClass('RenderView') {}

__PACKAGE__->meta->make_immutable;

1;

Before I mention a few things in the controller, let's run the application:

$ ./script/myfirstgrid_server.pl -d -r -k

When we open our browser with http://0.0.0.0:3000/ our Catalyst console gives us our browser's GET request:

[debug] "GET" request for "getdata" from "127.0.0.1"
[debug] Query Parameters are:
.-------------------------------------+--------------------------------------.
| Parameter                           | Value                                |
+-------------------------------------+--------------------------------------+
| _search                             | false                                |
| nd                                  | 1347390094273                        |
| page                                | 1                                    |
| rows                                | 10                                   |
| sidx                                | inv_id                               |
| sord                                | desc                                 |
'-------------------------------------+--------------------------------------'

We should see our grid filled with data in the browser, but we don't yet have the ability to edit it.

We need to edit our template to call editurl and setup it's options:

root/index.tt:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>My First Grid</title>

<link rel="stylesheet" type="text/css" media="screen" href="[% c.uri_for('/static/css/cupertino/jquery-ui-1.8.22.custom.css') %]" />
<link rel="stylesheet" type="text/css" media="screen" href="[% c.uri_for('/static/css/ui.jqgrid.css') %]" />

<style type="text/css">
html, body {
  margin: 0;
  padding: 0;
  font-size: 75%;
}
</style>

<script src="[% c.uri_for('/static/js/jquery-1.7.2.min.js') %]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/js/i18n/grid.locale-en.js') %]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/js/jquery.jqGrid.min.js') %]" type="text/javascript"></script>

<script type="text/javascript">
$(function(){ 

  $("#list").jqGrid({
    url:"[% c.uri_for("getdata") %]",
    datatype:'json',
    mtype:'GET',
    colNames:['Inv No', 'Client ID', 'Amount','Tax','Total','Notes'],
    colModel:[ 
      {name:'inv_id', index:'inv_id', width:55}, 
      {name:'client_id', index:'client_id', width:55},
      {name:'amount', index:'amount', width:80, align:'right'}, 
      {name:'tax', index:'tax', width:80, align:'right'}, 
      {name:'total', index:'total', width:80, align:'right'}, 
      {name:'note', index:'note', width:150, sortable:false} 
    ],
    pager:'#pager',
    rowNum:10,
    rowList:[10,20,30],
    sortname:'inv_id',
    sortorder:'desc',
    viewrecords:true,
    gridview:true,
    caption:'My First Grid',
    editurl:"[% c.uri_for("postrow") %]" 
  }); 

  jQuery("#list").jqGrid('navGrid','#pager',
    {}, //options
    {height:280,reloadAfterSubmit:false}, // edit options
    {height:280,reloadAfterSubmit:false}, // add options
    {reloadAfterSubmit:false}, // del options
    {} // search options
  ); 

}); 
</script>
</head>

<body>
<table id="list"><tr><td/></tr></table> 
<div id="pager"></div> 
</body>
</html>

And we need to add the postrow method to the root controller:

Controller/Root.pm:

sub postrow :Local {
  my ($self, $c) = @_;

  my $data = $c->req->params;
  my $inv_rs = $c->model('DB::Inventory')->search({inv_id => $data->{inv_id}});
  $inv_rs->update({
    client_id => $data->{client_id},
    amount => $data->{amount},
    tax => $data->{tax}, 
    total => $data->{total}, 
    note => $data->{note}, 
  });
  $c->response->status(204); 
}

Run it and see what you get.

You should see a change at the bottom of the grid, with the addition of add, edit, delete, search and refresh icons.

Select a row by clicking on it, then select the edit icon and submit the row to be saved. Note you were not allowed to edit anything and the POST did not provide any parameters other than 'oper':

[debug] "POST" request for "postrow" from "127.0.0.1"
[debug] Body Parameters are:
.-------------------------------------+--------------------------------------.
| Parameter                           | Value                                |
+-------------------------------------+--------------------------------------+
| oper                                | edit                                 |
'-------------------------------------+--------------------------------------'
[debug] Response Code: 204;

I wanted it to do this on purpose. I want you to see that parameters POSTed correspond to jqGrid columns that are editable. In our template we now have:

colModel:[ 
  {name:'inv_id', index:'inv_id', width:55}, 
  {name:'client_id', index:'client_id', width:55},
  {name:'amount', index:'amount', width:80, align:'right'}, 
  {name:'tax', index:'tax', width:80, align:'right'}, 
  {name:'total', index:'total', width:80, align:'right'}, 
  {name:'note', index:'note', width:150, sortable:false} 
],

Simply making them editable should cause them to be POSTed for use by our controller:

colModel :[ 
  {name:'inv_id', index:'inv_id', width:55, editable:true, editoptions:{size:10}}, 
  {name:'client_id', index:'client_id', width:55, editable:true, editoptions:{size:10}},
  {name:'amount', index:'amount', width:80, align:'right', editable:true, editoptions:{size:10}}, 
  {name:'tax', index:'tax', width:80, align:'right', editable:true, editoptions:{size:10}}, 
  {name:'total', index:'total', width:80, align:'right', editable:true, editoptions:{size:10}}, 
  {name:'note', index:'note', width:150, sortable:false, editable: true, edittype:"textarea", editoptions:{rows:"2",cols:"20"}} 
],

Making these changes and running the application, the parameters we need are now POSTed and we are able to edit our data:

[debug] "POST" request for "postrow" from "127.0.0.1"
[debug] Body Parameters are:
.-------------------------------------+--------------------------------------.
| Parameter                           | Value                                |
+-------------------------------------+--------------------------------------+
| amount                              | 135.00                               |
| client_id                           | 8221                                 |
| id                                  | 6                                    |
| inv_id                              | 33                                   |
| note                                | This is record 33.                   |
| oper                                | edit                                 |
| tax                                 | 77.34                                |
| total                               | 213.32                               |
'-------------------------------------+--------------------------------------'
[debug] Response Code: 204;

Here is our template and controller:

root/index.tt:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>My First Grid</title>

<link rel="stylesheet" type="text/css" media="screen" href="[% c.uri_for('/static/css/cupertino/jquery-ui-1.8.22.custom.css') %]" />
<link rel="stylesheet" type="text/css" media="screen" href="[% c.uri_for('/static/css/ui.jqgrid.css') %]" />

<style type="text/css">
html, body {
  margin: 0;
  padding: 0;
  font-size: 75%;
}
</style>

<script src="[% c.uri_for('/static/js/jquery-1.7.2.min.js') %]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/js/i18n/grid.locale-en.js') %]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/js/jquery.jqGrid.min.js') %]" type="text/javascript"></script>

<script type="text/javascript">
$(function(){ 

  $("#list").jqGrid({
    url:"[% c.uri_for("getdata") %]",
    datatype:'json',
    mtype:'GET',
    colNames:['Inv No', 'Client ID', 'Amount','Tax','Total','Notes'],
    colModel :[ 
      {name:'inv_id', index:'inv_id', width:55, editable:true, editoptions:{size:10}}, 
      {name:'client_id', index:'client_id', width:55, editable:true, editoptions:{size:10}},
      {name:'amount', index:'amount', width:80, align:'right', editable:true, editoptions:{size:10}}, 
      {name:'tax', index:'tax', width:80, align:'right', editable:true, editoptions:{size:10}}, 
      {name:'total', index:'total', width:80, align:'right', editable:true, editoptions:{size:10}}, 
      {name:'note', index:'note', width:150, sortable:false, editable: true, edittype:"textarea", editoptions:{rows:"2",cols:"20"}} 
    ], 
    pager:'#pager',
    rowNum:10,
    rowList:[10,20,30],
    sortname:'inv_id',
    sortorder:'desc',
    viewrecords:true,
    gridview:true,
    caption:'My First Grid',
    editurl:"[% c.uri_for("postrow") %]" 
  }); 

  jQuery("#list").jqGrid('navGrid','#pager',
    {}, //options
    {height:280,reloadAfterSubmit:false}, // edit options
    {height:280,reloadAfterSubmit:false}, // add options
    {reloadAfterSubmit:false}, // del options
    {} // search options
  ); 

}); 
</script>
</head>

<body>
<table id="list"><tr><td/></tr></table> 
<div id="pager"></div> 
</body>
</html>

Controller/Root.pm:

package MyFirstGrid::Controller::Root;
use Moose;
use namespace::autoclean;

BEGIN { extends 'Catalyst::Controller' }
with 'Catalyst::TraitFor::Controller::jQuery::jqGrid'; 

__PACKAGE__->config(namespace => '');

sub index :Path :Args(0) {
  my ($self, $c) = @_;

  $c->detach($c->view("TT"));
}

sub getdata :Local {
  my ($self, $c) = @_;

  my $inv_rs = $c->model('DB::Inventory')->search({});
  $inv_rs = $self->jqgrid_page($c, $inv_rs);
  my @row_data;
  while (my $inv = $inv_rs->next) {
    my $single_row = {
      cell => [
        $inv->inv_id,
        $inv->client_id,
        $inv->amount,
        $inv->tax,
        $inv->total,
        $inv->note,
      ],
    };
    push @row_data, $single_row;
  }
  $c->stash->{json_data}{rows} = \@row_data;
  $c->stash->{current_view} = 'JSON'; 
}

sub postrow :Local {
  my ($self, $c) = @_;

  my $data = $c->req->params;
  my $inv_rs = $c->model('DB::Inventory')->search({inv_id => $data->{inv_id}});
  $inv_rs->update({
    client_id => $data->{client_id},
    amount => $data->{amount},
    tax => $data->{tax}, 
    total => $data->{total}, 
    note => $data->{note}, 
  });
  $c->response->status(204); 
} 

sub default :Path {
  my ($self, $c) = @_;

  $c->response->body('Page not found');
  $c->response->status(404);
}

# Attempt to render a view, if needed.

sub end :ActionClass('RenderView') {}

__PACKAGE__->meta->make_immutable;

1;

Improve 'edit' and Add 'add' and 'delete'

Notice what is POSTed when we try to add a row:

[debug] "POST" request for "postrow" from "127.0.0.1"
[debug] Body Parameters are:
.-------------------------------------+--------------------------------------.
| Parameter                           | Value                                |
+-------------------------------------+--------------------------------------+
| amount                              | 2                                    |
| client_id                           | 2                                    |
| id                                  | _empty                               |
| inv_id                              | 2                                    |
| note                                | 2                                    |
| oper                                | add                                  |
| tax                                 | 2                                    |
| total                               | 2                                    |
'-------------------------------------+--------------------------------------'
[debug] Response Code: 204;

We are almost getting what we need POSTed, but we are going to make a few changes. First of all, editing inv_id is not something we should be able to do, so let's make inv_id not editable:

colModel :[ 
  {name:'inv_id', index:'inv_id', width:55, editable:false}, 
  {name:'client_id', index:'client_id', width:55, editable:true, editoptions:{size:10}},
  {name:'amount', index:'amount', width:80, align:'right', editable:true, editoptions:{size:10}}, 
  {name:'tax', index:'tax', width:80, align:'right', editable:true, editoptions:{size:10}}, 
  {name:'total', index:'total', width:80, align:'right', editable:true, editoptions:{size:10}}, 
  {name:'note', index:'note', width:150, sortable:false, editable: true, edittype:"textarea", editoptions:{rows:"2",cols:"20"}} 
],

Let's run it and see what we get now when we add a row:

[debug] "POST" request for "postrow" from "127.0.0.1"
[debug] Body Parameters are:
.-------------------------------------+--------------------------------------.
| Parameter                           | Value                                |
+-------------------------------------+--------------------------------------+
| amount                              | 2                                    |
| client_id                           | 2                                    |
| id                                  | _empty                               |
| note                                | 2                                    |
| oper                                | add                                  |
| tax                                 | 2                                    |
| total                               | 2                                    |
'-------------------------------------+--------------------------------------'
[debug] Response Code: 204;

We no longer are able to edit inv_id so let's put our code to add rows into our controller:

sub postrow :Local {
  my ($self, $c) = @_;

  my $data = $c->request->parameters;
  if ($data->{oper} eq 'edit') { # save row 
    my $inv_rs = $c->model('DB::Inventory')->search({inv_id => $data->{id}});
    $inv_rs->update({
      client_id => $data->{client_id},
      amount => $data->{amount},
      tax => $data->{tax}, 
      total => $data->{total}, 
      note => $data->{note}, 
    });
    $c->response->status(204); 
  }
  elsif ($data->{oper} eq 'add') { # add new row
    my $inv_rs = $c->model('DB::Inventory');
    $inv_rs->create({
      client_id => $data->{client_id},
      amount => $data->{amount},
      tax => $data->{tax}, 
      total => $data->{total}, 
      note => $data->{note}, 
    });
    $c->response->status(204); 
  }  
}

Add a new row and see what you get:

[debug] "POST" request for "postrow" from "127.0.0.1"
[debug] Body Parameters are:
.-------------------------------------+--------------------------------------.
| Parameter                           | Value                                |
+-------------------------------------+--------------------------------------+
| amount                              | 2                                    |
| client_id                           | 2                                    |
| id                                  | _empty                               |
| note                                | 2                                    |
| oper                                | add                                  |
| tax                                 | 2                                    |
| total                               | 2                                    |
'-------------------------------------+--------------------------------------'
[debug] Response Code: 204;

The POST looks good, and you should be able to page through your data and observe that the new row looks OK. But try to edit a row and when you submit it you will have problems. Take a look at my console:

[debug] "POST" request for "postrow" from "127.0.0.1"
[debug] Body Parameters are:
.-------------------------------------+--------------------------------------.
| Parameter                           | Value                                |
+-------------------------------------+--------------------------------------+
| amount                              | 2.00                                 |
| client_id                           | 2222                                 |
| id                                  | 1                                    |
| note                                | 2                                    |
| oper                                | edit                                 |
| tax                                 | 2.00                                 |
| total                               | 2.00                                 |
'-------------------------------------+--------------------------------------'
[debug] Response Code: 204;

The 'id' parameter is of no use to me, I need inv_id to save the correct row. 'id' is the index jqGrid uses to identify a particular row in the grid.

Thanks to Oleg at http://stackoverflow.com we have a solution. Set key:true in jqGrid's inv_id definition so that jqGrid POSTs inv_id rather than 'id':

colModel :[ 
  {name:'inv_id', index:'inv_id', width:55, editable:false, key:true}, 
  {name:'client_id', index:'client_id', width:55, editable:true, editoptions:{size:10}},
  {name:'amount', index:'amount', width:80, align:'right', editable:true, editoptions:{size:10}}, 
  {name:'tax', index:'tax', width:80, align:'right', editable:true, editoptions:{size:10}}, 
  {name:'total', index:'total', width:80, align:'right', editable:true, editoptions:{size:10}}, 
  {name:'note', index:'note', width:150, sortable:false, editable: true, edittype:"textarea", editoptions:{rows:"2",cols:"20"}} 
],

Edit a row and the parameter POSTed is still named 'id', but now it's value is that of inv_id for that row:

[debug] "POST" request for "postrow" from "127.0.0.1"
[debug] Body Parameters are:
.-------------------------------------+--------------------------------------.
| Parameter                           | Value                                |
+-------------------------------------+--------------------------------------+
| amount                              | 2.00                                 |
| client_id                           | 2222                                 |
| id                                  | 39                                   |
| note                                | 2                                    |
| oper                                | edit                                 |
| tax                                 | 2.00                                 |
| total                               | 2.00                                 |
'-------------------------------------+--------------------------------------'
[debug] Response Code: 204;

Adding a row should still work too, but now see what happens when you try to delete one:

[debug] "POST" request for "postrow" from "127.0.0.1"
[debug] Body Parameters are:
.-------------------------------------+--------------------------------------.
| Parameter                           | Value                                |
+-------------------------------------+--------------------------------------+
| id                                  | 39                                   |
| oper                                | del                                  |
'-------------------------------------+--------------------------------------'
[warn] Calling $c->view() will return a random view unless you specify one of:
[warn] * $c->config(default_view => "the name of the default view to use")
[warn] * $c->stash->{current_view} # the name of the view to use for this request
[warn] * $c->stash->{current_view_instance} # the instance of the view to use for this request
[warn] NB: in version 5.81, the "random" behavior will not work at all.
[debug] Rendering template "postrow.tt"
[error] Couldn't render template "postrow.tt: file error - postrow.tt: not found"
[error] Couldn't render template "postrow.tt: file error - postrow.tt: not found"
[debug] Response Code: 500;

The POST parameters are correct, let's add the code to the controller. Add the 'delete' condition to postrow:

elsif ($data->{oper} eq 'del') { # delete row
  my $inv_rs = $c->model('DB::Inventory')->search({inv_id => $data->{id}});
  $inv_rs->delete();
  $c->response->status(204); 
}
else {
  $c->response->body('400 BAD REQUEST: Root/postrow');
  $c->response->status(400); # 400 BAD REQUEST 
}

We now have:

sub postrow :Local {
  my ($self, $c) = @_;

  my $data = $c->request->parameters;
  if ($data->{oper} eq 'edit') { # save row 
    my $inv_rs = $c->model('DB::Inventory')->search({inv_id => $data->{id}});
    $inv_rs->update({
      client_id => $data->{client_id},
      amount => $data->{amount},
      tax => $data->{tax}, 
      total => $data->{total}, 
      note => $data->{note}, 
    });
    $c->response->status(204); 
  }
  elsif ($data->{oper} eq 'add') { # add new row
    my $inv_rs = $c->model('DB::Inventory');
    $inv_rs->create({
      client_id => $data->{client_id},
      amount => $data->{amount},
      tax => $data->{tax}, 
      total => $data->{total}, 
      note => $data->{note}, 
    });
    $c->response->status(204); 
  }  
  elsif ($data->{oper} eq 'del') { # delete row
    my $inv_rs = $c->model('DB::Inventory')->search({inv_id => $data->{id}});
    $inv_rs->delete();
    $c->response->status(204); 
  }
  else {
    $c->response->body('400 BAD REQUEST: Root/postrow');
    $c->response->status(400); # 400 BAD REQUEST 
  } 
}

Final Version

root/index.tt:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>My First Grid</title>

<link rel="stylesheet" type="text/css" media="screen" href="[% c.uri_for('/static/css/cupertino/jquery-ui-1.8.22.custom.css') %]" />
<link rel="stylesheet" type="text/css" media="screen" href="[% c.uri_for('/static/css/ui.jqgrid.css') %]" />

<style type="text/css">
html, body {
  margin: 0;
  padding: 0;
  font-size: 75%;
}
</style>

<script src="[% c.uri_for('/static/js/jquery-1.7.2.min.js') %]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/js/i18n/grid.locale-en.js') %]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/js/jquery.jqGrid.min.js') %]" type="text/javascript"></script>

<script type="text/javascript">
$(function(){ 

  $("#list").jqGrid({
    url:"[% c.uri_for("getdata") %]",
    datatype:'json',
    mtype:'GET',
    colNames:['Inv No', 'Client ID', 'Amount','Tax','Total','Notes'],
    colModel :[ 
      {name:'inv_id', index:'inv_id', width:55, editable:false, key:true}, 
      {name:'client_id', index:'client_id', width:55, editable:true, editoptions:{size:10}},
      {name:'amount', index:'amount', width:80, align:'right', editable:true, editoptions:{size:10}}, 
      {name:'tax', index:'tax', width:80, align:'right', editable:true, editoptions:{size:10}}, 
      {name:'total', index:'total', width:80, align:'right', editable:true, editoptions:{size:10}}, 
      {name:'note', index:'note', width:150, sortable:false, editable: true, edittype:"textarea", editoptions:{rows:"2",cols:"20"}} 
    ], 
    pager:'#pager',
    rowNum:10,
    rowList:[10,20,30],
    sortname:'inv_id',
    sortorder:'desc',
    viewrecords:true,
    gridview:true,
    caption:'My First Grid',
    editurl:"[% c.uri_for("postrow") %]" 
  }); 

  jQuery("#list").jqGrid('navGrid','#pager',
    {}, //options
    {height:280,reloadAfterSubmit:false}, // edit options
    {height:280,reloadAfterSubmit:false}, // add options
    {reloadAfterSubmit:false}, // del options
    {} // search options
  ); 

}); 
</script>
</head>

<body>
<table id="list"><tr><td/></tr></table> 
<div id="pager"></div> 
</body>
</html>

Controller/Root.pm:

package MyFirstGrid::Controller::Root;
use Moose;
use namespace::autoclean;

BEGIN { extends 'Catalyst::Controller' }
with 'Catalyst::TraitFor::Controller::jQuery::jqGrid'; 

__PACKAGE__->config(namespace => '');

sub index :Path :Args(0) {
  my ($self, $c) = @_;

  $c->detach($c->view("TT"));
}

sub getdata :Local {
  my ($self, $c) = @_;

  my $inv_rs = $c->model('DB::Inventory')->search({});
  $inv_rs = $self->jqgrid_page($c, $inv_rs);
  my @row_data;
  while (my $inv = $inv_rs->next) {
    my $single_row = {
      cell => [
        $inv->inv_id,
        $inv->client_id,
        $inv->amount,
        $inv->tax,
        $inv->total,
        $inv->note,
      ],
    };
    push @row_data, $single_row;
  }
  $c->stash->{json_data}{rows} = \@row_data;
  $c->stash->{current_view} = 'JSON'; 
}

sub postrow :Local {
  my ($self, $c) = @_;

  my $data = $c->request->parameters;
  if ($data->{oper} eq 'edit') { # save row 
    my $inv_rs = $c->model('DB::Inventory')->search({inv_id => $data->{id}});
    $inv_rs->update({
      client_id => $data->{client_id},
      amount => $data->{amount},
      tax => $data->{tax}, 
      total => $data->{total}, 
      note => $data->{note}, 
    });
    $c->response->status(204); 
  }
  elsif ($data->{oper} eq 'add') { # add new row
    my $inv_rs = $c->model('DB::Inventory');
    $inv_rs->create({
      client_id => $data->{client_id},
      amount => $data->{amount},
      tax => $data->{tax}, 
      total => $data->{total}, 
      note => $data->{note}, 
    });
    $c->response->status(204); 
  }  
  elsif ($data->{oper} eq 'del') { # delete row
    my $inv_rs = $c->model('DB::Inventory')->search({inv_id => $data->{id}});
    $inv_rs->delete();
    $c->response->status(204); 
  }
  else {
    $c->response->body('400 BAD REQUEST: Root/postrow');
    $c->response->status(400); # 400 BAD REQUEST 
  } 
} 

sub default :Path {
  my ($self, $c) = @_;

  $c->response->body('Page not found');
  $c->response->status(404);
}

# Attempt to render a view, if needed.

sub end :ActionClass('RenderView') {}

__PACKAGE__->meta->make_immutable;

1;

Thanks

Thanks to the authors of the llama and alpaca books, the Pittsburgh Perl Workshop, and the developers of Perldoc::Server for helping me get started.

Thanks to t0m for his "Heavy Tools", and others at irc.perl.org who've helped me begin to learn Perl: rafl, kd, osfameron (moof!), gshank, mst, dpetrov, hobbs, joel.

Thanks to Devin Austin for his jqGrid and Catalyst::Controller::REST tutorial in the Catalyst Advent Calender, and for Grimlock::Web and correspondence.

Thanks to Oleg at http://stackoverflow.com for help with jqGrid.

About

My name is Nolan Joseph Axford and I live in Dover-Foxcroft, Maine—you can call me j0e. As of September 11th, 2012 I am 52 years old learning Perl after coding real-time embedded systems with Assembly and C. It's been 10 years since I wrote any code. My Perl skills are a speck of sand on a very large beach, I'm a 95 lb. Perl weakling with sand blasting into my face.

I am trying to gain enough Perl skill to find work. I would especially like to try living and working outside the United States.