web2py[:]


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

WebGrid for web2py
Recipe by Nathan Freeze (mrfreeze) on 2009-12-04 in Module

This is a module that lets you build a table on the server that supports paging, sorting, editing and totals easily. It requires no javascript or sessions to work.

*UPDATE* This module is no longer maintained. Please use SQLFORM.grid instead.

Add the webgrid.py module to your modules folder (download at bottom)

In your model:

webgrid = local_import('webgrid')

In your controller:

def index():
    grid = webgrid.WebGrid(crud)
    grid.datasource = db(db.things.id>0)
    grid.pagesize = 10
    return dict(grid=grid()) #notice the ()

The datasource can be a Set, Rows, Table, or list of Table. Joins are also supported.

grid.datasource = db(db.things.id>0) #Set
grid.datasource = db(db.things.id>0).select() #Rows
grid.datasource = db.things #Table
grid.datasource = [db.things,db.others] #list of Table
grid.datasource = db(db.things.id==db.others.thing)# join

The main row components of the WebGrid are header, filter, datarow, pager, page_total, footer

You can link to crud functions using action_links. Just tell it where crud is exposed:

grid.crud_function = 'data'

You can turn rows on and off:

grid.enabled_rows = ['header','filter', 'pager','totals','footer','add_links']

You can control the fields and field headers:

grid.fields = ['things.name','things.location','things.amount']
grid.field_headers = ['Name','Location','Amount']

You can control the action links (links to crud actions) and action headers:

grid.action_links = ['view','edit','delete']
grid.action_headers = ['view','edit','delete']

You will want to modify crud.settings.[action]_next so that it redirects to your WebGrid page after completing:

if request.controller == 'default' and request.function == 'data':
    if request.args:
        crud.settings[request.args(0)+'_next'] = URL(r=request,f='index')

You can get page totals for numeric fields:

grid.totals = ['things.amount']

You can set filters on columns:

grid.filters = ['things.name','things.created']

You can modify the Query that filters use (not available if your datasource is a Rows object, use rows.find):

grid.filter_query = lambda f,v: f==v

You can control which request vars are allowed to override the grid settings:

grid.allowed_vars = ['pagesize','pagenum','sortby','ascending','groupby','totals']

The WebGrid will use a field's represent function if present when rendering the cell. If you need more control, you can completely override the way a row is rendered.

The functions that render each row can be replaced with your own lambda or function:

grid.view_link = lambda row: ...
grid.edit_link = lambda row: ...
grid.delete_link = lambda row: ...
grid.header = lambda fields: ...
grid.datarow = lambda row: ...
grid.footer = lambda fields: ...
grid.pager = lambda pagecount: ...
grid.page_total = lambda:

Here are some useful variables for building your own rows:

grid.joined # tells you if your datasource is a join
grid.css_prefix # used for css
grid.tablenames
grid.response # the datasource result
grid.colnames # column names of datasource result
grid.pagenum
grid.pagecount
grid.total # the count of datasource result

For example, let's customize the footer:

grid.footer = lambda fields : TFOOT(TD("This is my footer" , 
                                                      _colspan=len(grid.action_links)+len(fields),
                                                      _style="text-align:center;"),
                                               _class=grid.css_prefix + '-webgrid footer')

You can also customize messages:

grid.messages.confirm_delete = 'Are you sure?'
grid.messages.no_records = 'No records'
grid.messages.add_link = '[add %s]'
grid.messages.page_total = "Total:"

You can also also use the row_created event to modify the row when it is created. Let's add a column to the header:

def on_row_created(row,rowtype,record):
    if rowtype=='header':
        row.components.append(TH(' '))

grid.row_created = on_row_created

Let's move the action links to the right side:

def links_right(tablerow,rowtype,rowdata):
    if rowtype != 'pager':
        links = tablerow.components[:3]
        del tablerow.components[:3]
        tablerow.components.extend(links)

grid.row_created = links_right

alt text

If you are using multiple grids on the same page, they must have unique names.

Download webgrid.py

Download demo App

grid table sorting paging

Related slices

Comments (112)

  • Login to post



  • 0
    mrfreeze 15 years ago
    Thanks to Fran for recent improvements. The WebGrid is now datatables compliant (you must disable the add_link though).

  • 0
    mrfreeze 15 years ago
    Better, stronger, faster. It now supports multiple grids per page!

  • 0
    benigno 15 years ago
    Very very nice, usefull and absolutelly reusable. One thing, anyway to remove links? grid.action_links = [] grid.action_headers = [] drops an error, and with an empty string, gets badly formated. A thing I'd love in here would be a search, (server side) searching in all columns with "contains" or something like that. But I guess thats probably in the pipeline already. Thanks for this great module.

  • 0
    mrfreeze 15 years ago
    You found a bug! It is fixed now. I'm working on the search feature as we speak :)

  • 0
    alexandreandrade 15 years ago
    I have a problem when my page uses request.args. The links at the grid's header don't work. So I used this workaround: -------------------------- if request.args(0): session.request_var = request.args(0) ..... your code return(grid=grid()) else: arg = request.function + '/' + session.request_var + '?' vars = '' for var in request.vars.keys(): vars= vars + var + '=' + request.vars[var] + '&' vars = vars[:-1] arg = arg + vars redirect(arg) ------------------- the important thing to change is the value of request.args(0) in session.your_request.var. Would be good if Webgrid detects if has request.args and correct the link at header of table.

  • 0
    mrfreeze 15 years ago
    I updated it to include the request.args. Please try when you have a chance. I also added column filters. I'll update the slice to include usage

  • 0
    mrfreeze 15 years ago
    It now handles filter query cache and page size.

  • 0
    thadeusb 15 years ago
    Do you have a sample css?

  • 0
    thadeusb 15 years ago
    If you are using the grid in a controller other than the one that you define with crud, it will not be able to sort by the column names/etc, since it is redirecting to the incorrect URL. if crud.settings.controller = default and webgrid defined in controller 'admin/list' webgrid will attempt to redirect to 'default/list?vars' instead of the desired 'admin/list?vars'

  • 0
    mrfreeze 15 years ago
    It's currently designed to work in the same controller that CRUD is exposed. I'll take a look at modifying it to handle different controllers. I don't have a CSS example at the moment but everything gets marked up with two classes during generation = '{name}-webgrid {component}. For example, the header in a webgrid named 'testgrid' would have the class 'testgrid-webgrid header'.

  • 0
    johannspies 15 years ago
    I am uncertain how to handle the controller part. I see the link to create a new entry refers to default/data/create/. So how do I define a 'create' function in the default controller? Also, I do not really understand your remark above: "You can link to crud functions using action_links. Just tell it where crud is exposed: grid.crud_function = 'data'" I suspect it is related, but could you explain a bit more for a beginner please? I am not sure how to interact with your module.

  • 0
    mrfreeze 15 years ago
    Webgrid uses CRUD which needs to be exposed in your controller somewhere (currently the same controller as webgrid): def data(): return dict(form=crud()) grid.crud_function tells the webgrid where your crud functions are exposed which in this case is the 'data' function. Crud is separate from webgrid. Look on page 230 (labeled 214) for more info.

  • 0
    mrfreeze 15 years ago
    @johann - 'create' is actually request.args(0) of the 'data' function in the 'default' controller. Hope that helps.

  • 0
    alexandreandrade 14 years ago
    I have a problem with the 'pager' funcionality. First, even when I have more rows than the grid.page_size, 'prev' and 'next' still mantains the link: http://hipercenter.com/convenios/convenios/convenios# Second, the page numbers don't work: http://hipercenter.com/convenios/default/convenios?convenios_pagenum=1 makes web2py generates a 'invalid function' page/error. looks web2py don't accept ? as the end of function in the link to separate convenios_pagenum as arg. how make it work?

  • 0
    alexandreandrade 14 years ago
    Solved: put on the function you are putting the grid crud.settings.controller = 'convenios' # and @auth.requires_login() def data(): return dict(form=crud())

  • 0
    frank 14 years ago
    thanks for the slice, I notice it's license of GPL V2, it mean if I use this, I need make open source of my project, right?

  • 0
    johannspies 14 years ago
    A further question about action links and 'def data()'. How can I define and use a separate function for each of the action links? I don't want to use "data". The "edit" link for example gave me the following error while the others not: TypeError: 'NoneType' object is not callable In the documentation I did not see that 'data' exposes any update function. Am I correct? So how do I define and use my own functions for "read", "edit", "delete", "create"?

  • 0
    mrfreeze 14 years ago
    @frank - the license is the same as web2py. You can distribute unmodified binary versions of webgrid provided that they acknowledge the author, me :) @johann - In the examples, 'data' is where I have exposed my crud functions. You can change it with grid.crud_function. It will use the standard crud function which will expose update so make sure all of your crud operations are working first. You can modify any of the crud function links in webgrid with lambdas (and any other part of the grid too): grid.view_link = lambda row: A(...) grid.edit_link = lambda row: A(...) grid.delete_link = lambda row: A(...) grid.add_links = lambda tables: TR(TD([A(...) for t in grid.tablenames]))

  • 0
    yamandu 14 years ago
    Thanks again!

  • 0
    yamandu 14 years ago
    It is not working when there is a reference field. Looks like it fails to parse webgrid to html. Why?

  • 0
    mrfreeze 14 years ago
    @yamandu - I'm looking into this but in the meantime try setting a represent attribute on your reference fields: db.myfield.represent = lambda v: v.name

  • 0
    mrfreeze 14 years ago
    Found it! It should work now. Thanks.

  • 0
    yamandu 14 years ago
    It works perfectly now! I put a represent to take a field from the referenced table and it looks great! From now I want to figure the datatables thing and need to localize webgrid bo Brazilian Portuguese. Thanks again and again Mr Freeze!

  • 0
    adsahay 14 years ago
    Firstly thanks for this module...this is awesome. I have a couple of newbie questions: 1) How can I make the action links appear after the date (in rightmost columns) instead of left? 2) Where do I modify crud.settings.[action]_next, in data?

  • 0
    mrfreeze 14 years ago
    @adsahay - This is the easiest way I can think to do it for question 1: def links_right(tablerow,rowtype,rowdata): if rowtype != 'pager': links = tablerow.components[:3] del tablerow.components[:3] tablerow.components.extend(links) grid.row_created = links_right For question 2, I would put it in your model.

  • 0
    mrfreeze 14 years ago
    BTW, 'grid.row_created = links_right' isn't part of the links_right function. It just got formatted that way. 'grid.row_created = links_right' should go in your controller where you instantiate WebGrid. Also, the reason to modify crud.settings.[action]_next in your model is that the models are executed on every request.

  • 0
    adsahay 14 years ago
    Thanks! Works well.

  • 0
    m3tr0 14 years ago
    Any plan to handle also legacy tables (i.e. tables without 'id' column)? Thank you!

  • 0
    frank 14 years ago
    if we use sqlite database of other name for example "guide.db" instead of default"db" , it does not work.

  • 0
    mrfreeze 14 years ago
    By saying 'guide.db', your are implying that db is an attribute of guide but guide has not been declared. Remove the period and it should work.

  • 0
    frank 14 years ago
    may be misunderstood, I make something like this, in model: guide=SQLDB("sqlite://guide.db") guide.define_table('guidepost', SQLField('name'), .......) in controller, def index(): grid = webgrid.WebGrid(crud) grid.datasource = guide(guide.guidepost.id>0) grid.pagesize = 10 return dict(grid=grid()) with error "KeyError: 'guidepost'", it does not work. if I use default "db" database, it works.

  • 0
    mrfreeze 14 years ago
    I have a project using WebGrid with a database instance named 'musicdb' and it works. Can you possibly send me your project as a w2p file so I can debug?

  • 0
    rahuld 14 years ago
    Mr Freeze, The grid component works well. But slight ambiguity with crud interfaces for edit, view and delete. Here is my question. I want to link update_issue to update issues with id - http://127.0.0.1:8000/bugs/default/update_issue . now update_issue sits in default.py file. and this works well if I access it directly [http://127.0.0.1:8000/bugs/default/update_issue/1] and so on. This is the file where I have called webgrid component from I have specified 
     grid.crud_function='update_issues'
    1- How do I make it work automatically after clicking the edit link. what would be the exact code to key in for grid.view_link = lambda row: A(...) ..? 2- Can we disable action links completely? 3- Sometimes it showed me 404 NOT FOUND error thought initially I worked it out 4- Can you prepare a short syntax of this very useful component as a technical ref. In the above I was unclear if "data" was a folder or a file Thanks Rahul

  • 0
    rahuld 14 years ago
    Never Mind I figured it out over webgrid group -- here is what I did so that new users are clear - Remember the example below is for datasource that is not a join. ---- here is the update issues stub -- Here i have defined my crud interface this is done in default.py 
    def update_issues():
    mycrud = Crud(globals(), db)
    return dict(form=mycrud.update(db.issues, request.args(0)),
    now I've created update_issues.html already while calling this function using {{=form}} Here is the link that calls this function in default.py itself ... 
    grid.edit_link= lambda row: A('edit', _href= mycrud.url(f='update_issues', args=[row['id']]))
    Now when ever I go to the link where I've defined my webgrid component and click edit, I get an editable page for my db. Its so cool.. Finally I thought this could help other novice users.

  • 0
    kroitus 14 years ago
    Hello. Sorry for maybe dumb question, but I have simple situation: Two tables something like that: 
    db.define_table('category',
   Field('name'))

db.define_table('expenses',
   Field('amount', 'double'),
   Field('id_category', db.category, required=False),
   Field('user'))
    I want to display expenses.amount and category.name in one table, but only from specific user. When I use db(db.expenses.id_category==db.category.id), I can easily display field category.name, but it displays records from all users. When I use db(db.expenses.id_users=='user'), it selects records from specific user, but it only displays expenses.id_category. Tried expenses.id_category.name - doesn't work.

  • 0
    mrfreeze 14 years ago
    Not a dumb question. Try this: 
    db.expenses.id_category.represent = lambda category: category.name
query = (db.expenses.id_category==db.category.id) & (db.expenses.id_users=='user')
grid.datasource = db(query)

  • 0
    kroitus 14 years ago
    Thanks. It worked. But now I have other problem. When I display all those records, edit and delete links are pointing not to record from expenses table, but from table category

  • 0
    mrfreeze 14 years ago
    You can override the links to point to the expenses table: 
    
grid.view_link = lambda row: A('view', _href=crud.url(f=grid.crud_function, args=['read', 'expenses', row['expenses']['id'] ))
grid.edit_link = lambda row: A('edit', _href=crud.url(f=grid.crud_function, args=['update', 'expenses', row['expenses']['id'] ))
grid.delete_link = lambda row: A('delete', _href=crud.url(f=grid.crud_function, args=['delete', 'expenses', row['expenses']['id'] ))

  • 0
    kroitus 14 years ago
    Thank You! And now - maybe it is possible to make range filter? For example - from one date to another?

  • 0
    apardue 14 years ago
    Hi - I have been using the grid for a while throughout a site I am building, and everything is working fine. However, when I tried to style it with CSS I was having no luck. In an attempt to get the simplest set up to identify what I'm doing wrong, I started a fresh web2py app called 'grid' and imported the webgrid module. I then and put in the model: db.define_table('mytable', Field('myname','string'), Field('mydob','date')) webgrid = local_import('webgrid') And in the default controller: def index(): grid = webgrid.WebGrid(crud) grid.datasource = db(db.mytable.id>0) grid.pagesize = 10 grid.footer = lambda fields : TFOOT(TD("This is my footer" , _colspan=len(grid.action_links)+len(fields), _style="text-align:center;"), _class=grid.css_prefix + '-webgrid footer') return dict(grid=grid()) And in the base.css: .grid-webgrid footer { background-color: blue; font-family: Arial; } The grid works fine, but the css isn't being applied. Can anyone help point out what I'm doing wrong? I'm pretty new to css and can't for the life figure out where I've messed up. Great slice - thanks in advance.

  • 0
    mrfreeze 14 years ago
    Have you set the grid.css_prefix? If not, the css_prefix will default to the name of the action that serves the grid (index in your case) so you would need to use .index-webgrid footer

  • 0
    apardue 14 years ago
    Ah, I had thought the name defaulted to the name of the grid rather than the controller (so .grid in my case rather than .index). I tried just now repeating the above but substituting the css as below, but it still didn't work. 
    .index-webgrid footer {
    font-family: Arial;
    color: blue;
    }
    I then set the grid.css_prefix: 
        grid.css_prefix = "index"
    But that also didn't effect anything. I just can't spot what I've missed. Thanks for your help.

  • 0
    apardue 14 years ago
    Having just retried I am able to style the grid as a whole using index-webgrid {}. But am still unable to modify the components within it.

  • 0
    apardue 14 years ago
    I was copying the above layout: {name}-webgrid {component}, when it should have been {name}-webgrid.{component} All sorted now and working - thanks.

  • 0
    yamandu 14 years ago
    Could you give some tips how to use webgrid with datatables and with LOAD?

  • 0
    yamandu 14 years ago
    Need help! I am getting AttributeError: 'list' object has no attribute 'find' in line 178 rows = rows.find(lambda row: row[flt_t][flt_f]==v) All grids that have single tables as datasources work perfectly but this has a join query as source. It crashes only when I try to use filter. Is that a bug or something else?

  • 0
    mrfreeze 14 years ago
    I think this is a bug in web2py's sql.py. I will post on the user group. Rows.find should always return a Rows object even if it is empty, not a list.

  • 0
    mrfreeze 14 years ago
    @yamandu - I sent a patch but in the meantime, you can just handle if your datasource is a list object manually.

  • 0
    yamandu 14 years ago
    @mr.freeze - I solved it by not getting the rows, I mean, not doing .select() I passed only the query and it worked. So to define what fields would be visible I used the grid.fields settings.

  • 0
    frank 14 years ago
    hi, mr.Freeze I got trouble on filter function, I have a database named db.invoice I define grid.datasource = db(db.invoice.entity==session.auth.user.id)(db.invoice.id>0) and grid.filters = ['invoice.name'] webgrid list works well. the problem is in the dropdown list of filter of invoice.name, it display all names of db.invoice, actually, it should display only those names by condition in datasource. I do not why?please advice. Frank

  • 0
    mrfreeze 14 years ago
    @frank - try changing the grid.filter_query to limit it to the values you want.

  • 0
    adsahay 14 years ago
    I have a datasource which has "sum", like: 
    grid.datasource = db(db.things.id>0).select(db.things.inventory.sum())
    I get a key error in grid. How do I fix this?

  • 0
    ammz 14 years ago
    Hi mr.freeze: I have the same problem that frank 2010-07-08. Please, Can you give us a example about how to use grid.filter_query?

  • 0
    mrfreeze 14 years ago
    Whoops, grid.filter_query doesn't do what I thought. It is for controlling the query for filter results, not filter items. I'll have to add this.

  • 0
    mrfreeze 14 years ago
    Okay, I posted a new version. You can now do: 
    grid.filter_items_query = lambda field: (db.invoice.entity==session.auth.user.id)&(field['id']>0)
    I also fixed a bug with clearing the filters.

  • 0
    ammz 14 years ago
    Thank's a lot, mr.freeze

  • 0
    frank 14 years ago
    thanks, mr.freeze, I'm just look into the source code of webgrid to figture that out. you save my time.

  • 0
    frank 14 years ago
    I still have other problem, if I have in model db.define_table('task', Field('title',length=128), ...... Field('man_in_charge',db.person,default=db.person.id,writable=True,readable=True), Field('description','text'), ) in controller, grid.fields = ['task.title','man_in_charge'] I got error as followings, if I move away man_in_charge in grid fields, it's ok. Traceback (most recent call last): File "D:\web2py cookbook\web2py\gluon\restricted.py", line 173, in restricted exec ccode in environment File "D:/web2py cookbook/web2py/applications/nano/controllers/default.py", line 659, in File "D:\web2py cookbook\web2py\gluon\globals.py", line 96, in self._caller = lambda f: f() File "D:\web2py cookbook\web2py\gluon\tools.py", line 1652, in f return action(*a, **b) File "D:/web2py cookbook/web2py/applications/nano/controllers/default.py", line 391, in list_task return dict(grid=grid(),person=person,form=form) File "D:\web2py cookbook\web2py\applications\nano\modules\webgrid.py", line 442, in __call__ r = row._extra[colname] File "D:\web2py cookbook\web2py\gluon\sql.py", line 664, in __getattr__ return dict.__getitem__(self,key) KeyError: '_extra' thanks for your advice

  • 0
    mrfreeze 14 years ago
    I think you need to include the table name with man_in_charge: 
    grid.fields = ['task.title','task.man_in_charge']

  • 0
    frank 14 years ago
    oh, sorry for such mistake to borther you. thanks,

  • 0
    adsahay 14 years ago
    Hi mr.freeze, Any help on my query dated 2010-07-09 (including sum in fields)?

  • 0
    mrfreeze 14 years ago
    @adsahay - This isn't currently supported. I'll see what I can do to add it.

  • 0
    villas 14 years ago
    is there a way, how can I specify default(initial) sort order?

  • 0
    villas 14 years ago
    I'm using WebGrid in Czech language application. I found there are several text strings not being externalized into settings. To change them, I have to modify source which is not good for upgrades. (e.g. previous, next and many others)

  • 0
    mrfreeze 14 years ago
    @david - You can use grid.sortby and grid.ascending to control the initial order. Which strings need to be externalized?
    • show more comments
    Hosting graciously provided by:
    Python Anywhere