WebGrid for web2py

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

Connect! and share your web2py slices
Facebook Google
Lost password
Create your account

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

Subscribe (0)

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

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

Comments (112)

Login to post
order by: newest oldest upvoted downvoted
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?
show more comments

© 2012 - web2pyslices.com web2py powered Movuca Social CMS Donate with Paypal

Hosting graciously provided by:

WebGrid for web2py

Related slices

Comments (112)