If you benefit from web2py hope you feel encouraged to pay it forward by contributing back to society in whatever form you choose!

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

alt text

jqGrid is a comprehensive data grid with many options and a robust api. This is just a simple example of how to get started.

Related slices

Comments (8)

  • Login to post



  • 0
    tim-richardson 1 year 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 2 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():  

    
    
    1. db.things.category.represent = lambda v: v.name
    2. fields = ['id','name','category','price','owner']

    ...

     


  • 0
    rvquartz 3 years ago
    Hello, Here is the controller (default.py): ---------------------------------------------------------------------------------- def index(): response.files.append(URL(r=request,c='static/css/ui-darkness',f='jquery-ui-1.8.12.custom.css')) response.files.append(URL(r=request,c='static/css',f='ui.jqgrid.css')) response.files.append(URL(r=request,c='static/js',f='jquery-ui-1.8.12.custom.min.js')) response.files.append(URL(r=request,c='static/src/i18n',f='grid.locale-en.js')) response.files.append(URL(r=request,c='static/js',f='jquery.jqGrid.min.js')) return dict() 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() @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 ================================================================================== here is the view (default/index.html): ----------------------------------------------------------------------------------
    ==================================================================================== I have verified that the db.category and db.things table is indeed populated with dummy data. This may be a dumb question - but i am new at javascript: What URL do I need to type into the browser to see the grid? when I type in "http://127.0.0.1:8000/grid_example1/default/index", all I get is a blank screen when I type in "", all I get is a message that says "Object does not exist"

  • 0
    pbnan 3 years ago
    Some missing parts in the code above: @tomyblack: there is no closing for < table id = "list" > < div id = "pager" >
    @mr.freeze Currently, in controller, order of included files should be different (it requires to copy "jquery-ui" to "/static/jqueryui" and "jqgrid" to "/static/jquery.jqGrid":
    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 4 years ago
    Hello, I finally got mr.freeze example to work after hunting down a studid typo. So I continued to tomyblacks example, but can´t get it to work. 1) is it so that the ceil function is not available in Python 2.5? 2) Error console on my firefox says "selector is not defined? Any ideas where to start looking for errors?
  • show more comments

    Hosting graciously provided by:
    Python Anywhere