Setting Up
Download jqGrid from here, extract it to your static folder and include it using response.files in your controller. It requires jQueryUI which we can link directly from google.
def index():
response.files.append("http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/jquery-ui.min.js")
response.files.append(URL(r=request,c='static/jqueryui/css/ui-darkness',f='jquery-ui-1.7.2.custom.css'))
response.files.append(URL(r=request,c='static/jquery.jqGrid/src/i18n',f='grid.locale-en.js'))
response.files.append(URL(r=request,c='static/jquery.jqGrid/js',f='jquery.jqGrid.min.js'))
response.files.append(URL(r=request,c='static/jquery.jqGrid/css',f='ui.jqgrid.css'))
return dict()
Let's build a test model and fill it with dummy data
from gluon.contrib.populate import populate
db.define_table('category',
Field('name'))
db.define_table('things',
Field('name'),
Field('quantity','integer'),
Field('owner'),
Field('price','double'),
Field('category',db.category))
if db(db.things.id>0).count() == 0:
populate(db.category,10)
populate(db.things,50)
Making it Work
Our test grid will live in the index view. Let's set it up to pull json data from an action called get_rows.
<script type="text/javascript">
jQuery(document).ready(function(){
jQuery("#list").jqGrid({
url:'{{=URL(r=request,f='call',args=['json','get_rows'])}}',
data: "{}",
datatype: 'json',
mtype: 'GET',
contentType: "application/json; charset=utf-8",
complete: function(jsondata, stat) {
if (stat == "success") {
var thegrid = jQuery("#list")[0];
thegrid.addJSONData(JSON.parse(jsondata.responseText).d);
}
},
colNames:['ID','Name','Category', 'Price','Owner'],
colModel :[
{name:'id',index:'id', width:55},
{name:'name', index:'name'},
{name:'category', index:'category'},
{name:'price', index:'price'},
{name:'owner', index:'owner'}
],
pager: '#pager',
rowNum:10,
rowList:[10,20,30],
sortname: 'name',
sortorder: 'desc',
viewrecords: true,
caption: 'Test Grid'
});
});
</script>
<table id="list"></table>
<div id="pager"></div>
Make sure you are exposing the service. This should be in your default controller.
def call():
"""
exposes services. for example:
http://..../[app]/default/call/jsonrpc
decorate with @services.jsonrpc the functions to expose
supports xml, json, xmlrpc, jsonrpc, amfrpc, rss, csv
"""
session.forget()
return service()
Now let's setup an action to serve json to the grid using our service. jqGrid requires a specific format for the json. Read the jqGrid docs for details.
@service.json
def get_rows():
db.things.category.represent = lambda v: v.name
fields = ['id','name','category','price','owner']
rows = []
page = int(request.vars.page)
pagesize = int(request.vars.rows)
limitby = (page * pagesize - pagesize,page * pagesize)
orderby = db.things[request.vars.sidx]
if request.vars.sord == 'desc': orderby = ~orderby
for r in db(db.things.id>0).select(limitby=limitby,orderby=orderby):
vals = []
for f in fields:
rep = db.things[f].represent
if rep:
vals.append(rep(r[f]))
else:
vals.append(r[f])
rows.append(dict(id=r.id,cell=vals))
total = db(db.things.id>0).count()
pages = int(total/pagesize)
#if total % pagesize == 0: pages -= 1
data = dict(total=pages,page=page,rows=rows)
return data
jqGrid is a comprehensive data grid with many options and a robust api. This is just a simple example of how to get started.
Comments (8)
0
tim-richardson 11 years ago
this works better in get_rows():
pages = math.ceil(1.0*total/pagesize)
because if total = 50 and pagesize = 30, you need two pages.
0
dngreguas 12 years ago
Hi, I have a problem when I build the method. It works when I have only one table, but I want to show all colummns from two tables using a join. Any idea.?
Controller
#How do they work?
def get_rows():
db.things.category.represent = lambda v: v.namefields = ['id','name','category','price','owner']...
0
rvquartz 13 years ago
0
pbnan 13 years ago
def index(): response.files.append(URL(r=request,c='static/jqueryui/css/smoothness',f='jquery-ui-1.8.11.custom.css')) response.files.append(URL(r=request,c='static/jquery.jqGrid/css',f='ui.jqgrid.css')) response.files.append(URL(r=request,c='static/jqueryui/js',f='jquery-ui-1.8.11.custom.min.js')) response.files.append(URL(r=request,c='static/jquery.jqGrid/src/i18n',f='grid.locale-en.js')) response.files.append(URL(r=request,c='static/jquery.jqGrid/js',f='jquery.jqGrid.min.js')) return dict()Edit: to change every edit window width + other features use the following on the beginning of your view.html:jQuery.extend(jQuery.jgrid.edit, { closeAfterAdd: true, closeAfterEdit: true, recreateForm: true, closeOnEscape:true, width:390 });0
ken0048 14 years ago