*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.
Comments (112)
- Login to post
order by: newest oldest upvoted downvoted
My table shows up on WebGrid, but the columns are much too wide to be useful. What is the best way to display 'thin' columns of integers.
db.define_table('Commodity_Risk',
Field('name','string'),
Field('capacity','string'),
Field('volatility','integer'),
Field('bid_spread','integer'),
Field('ask_spread','integer'),
Field('low_limit','integer'),
Field('high_limit','integer'),
Field('volatility_surface','upload'),
format = '%(name)s')
Tool is cool ;)
but i have question is there any why to add owne action ?
example:
i have list of users and i want to have link to send email to user (next to edit link)
There are a couple ways. The easiest is to probably just modify the row when it's created:
will webgrid work for the following table too ?
t=TABLE()
t.append(TR(TD(value1),TD(value2),TD(value3),TD(value4)))
...
...
...
grid.datasource=t
thanks in advance...
The datasource must be a Set or Rows object like:
Excelent
Can you do a treeview for web2py ?
best regards,
António
@paulgerrard - If you can send me your model I can give you examples.
Hi again. Maybe being dim - but I'm not sure what your last comment means.
Making filter_items_query the same as the datasource doesn't seem to have any effect on the values in the filter drop downs. Your comment "The filter_items_query isn't a subset of datasource." - that's what you are saying I guess.
You've said the filter_query is for the results, not the values in the filter - so that's probably not a solution.
So I'm thinking it is not possible to restrict the values in the filter drop downs to values that appear in the corresponding results column? Is that correct?
The problem I have is one of my filters is names of people in the user's organisation, but this is a shared system with users from other organisations. The datasource only shows data from the current user's organisation so I need to limit the filter values to maintain confidentiality across organisations. If I can't limit the values, I can't use webgrid :O(
Fixed. Thanks!
Thanks for this usefully module.
In this code:
grid.datasource = db(db.things.category==db.category.id)
grid.fields = ['things.id','things.category','things.name', 'things.owner']
I can't change 'things.category' by 'category.name' ? (add other field of 'category' table)
For many page, i had tiny changed: "for x in xrange(1, pagecount + 1)", instead:
p1 = 1 if (self.pagenum<5) else self.pagenum-4
p2 = pagecount + 1 if (self.pagenum > pagecount -4) else self.pagenum + 6
if (p2<11) & (pagecount >10): p2 = 11
for x in xrange(p1, p2):
I have a complicated query as a data source and the filter drop downs contain items that aren't in the records displayed in the grid. I understand I should use the 'grid.filter_items_query' field and ave done - but it seems to make no difference. I get many more entries in the drop down than are in the grid.
Here's the two lines of code that are relevant. Any ideas?
The filter_items_query isn't a subset of datasource. It's counter-intuitive but more flexible that way. You may need to modify the filter_query also.
Now, it's works.
Thank's a lot.
Sometimes we have armazon1 or lente1 or lc4
But with this code:
For some reason lc4 is null when it calls the represent function. I'm not sure if this is related to WebGrid. Can you try changing the represent for lc4 to this:
Good night sir.
Thanks a lot for this power full tool.
How about google application engine. I can't make it work on it. Do you have some advice about it?
I don't think joins are supported on GAE. Does your datasource involve a join? If not, what error are you getting?
Done. You can modify the label with:
Good idea. I'll add it.
Using the grid - like it! One of my users asked me if he could have a 'clear all filters' button so he lists all records (having multiple drop downs already selected). I said I'd check.
I know I could stick a link somewhere to revisit the page (as on first entry) to achieve this, but is there an option within Webgrid to display a button/link to do this?
@mr.freeze - Thank You! It's working now. PS. Is it possible to add version string to Webgrid, Please?
mr.freeze - if datasource defined as set, for example: db(db.things.id>0) #Set
I must define grid.fields = ['things.name','things.location','things.amount'] or i got error in webgrid line 203: idfield = self.fields[0].split('.')[0] +'.id' IndexError: list index out of range. If use table as datasource, all works fine, without setting grid.fields.
@smg - A recent change broke this. Please re-download and try again. It should be working now.
thank's a lot mr.freeze.
With new version of web2py(2010-08-02) and without pagos.id, it's ok.
I would like to suggest you, if it is possible to put a slide bar when we select many kind of registers, something like web2py->admin->database administration
Mr. Freeze, I would like to add the option to use Ajax search functionality to filter the columns. If I work on a patch would you integrate it as an option to WebGrid? Thanks!
In the case of large result sets, you may want to add an ellipsis for the page number list. For example, if you populate the database with 500 records, you will need to scroll to the right in order to see all of the page numbers, and it skews the output. So instead of listing 1, 2, 3, 4, 5, 6, etc., you would want to show 1, 2, 3, ..., 48, 49, 50.
@drayco - can you download webgrid.py and try again? You should be able to remove pagos.id without error.
This is odd. Can you do this as a test:
grid.view_link = lambda row: str(row.keys())
and tell me what it shows in the view link?
Also, add pagos.id to grid.fields.
Ok
Headers
In the list
If you like, I can send you an image and/or html field
Does the original grid.view_link work when id is in grid.fields?
With this in controller:
It's work.
However, Can I don't show the id?
No, I get this error
Hi, mr.freeze
I have a litle problem, with web2py 1.81.5 and webgrid(2010-07-09)
I can do this
In Model
In Controller
But with web2py 1.81.5 and webgrid(2010-07-26), I get error in this line grid.view_link= lambda row: A('view', _href= crud.url(f='show_entrega', args=[row['pagos']['id']])) Can I do something to avoid that error?
What error do you get?
ups, ok this is it
thank's in advanced
Does it work if you change?:
args=[row['pagos']['id']]
to:
args=[row['id']]
@david - you're right, I need to get this under proper version control. I wasn't expecting people to use it!
@yamandu - It's GPL2 so I encourage anyone to use and modify it. I actually wrote this at the request of Massimo to go into Crud but he hasn't had time to look at it.
Please try to add some version number into webgrid.py file so we can see what version we have.
Thanks for great plugin!!
David
In fact, this is not a plugin, as least as I know it.
It is a module.
But I always wanted to sugest or do myself this as a plugin.
I think it would be very valuable if we join it jqgrid.
We could eliminate weakness of both and join the strenght
That´s my opinion!
It takes over 8.5 seconds to render the response for a table containing 10,000 rows when talking to a MySQL database. I think I have identified "chattiness" in the code. If all fields are being selected (even though they will not be displayed as per the grid.fields parameter), I would not expect additional select statements outside of the COUNT(*) statement. The data is already in memory and just needs to be parsed. What does everyone think?
Model snippet:
Controller snippet:
Wireshark capture from client:
You are absolutely right! I updated it to select only the fields. Please add 'allstats.id' to your list of fields and try the updated module.
Getting better! Would it be possible to eliminate the last three SELECT statements, since the data is obtained using the first SELECT? I think this might eliminate the extra network traffic and processing time.
Wireshark capture:
These are for the filter row. I don't think there's a way to avoid making the extra selects since the filter values wouldn't be a subset of the datasource.
Thank you !!!!
is there a way, how can I specify default(initial) sort order?
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)
@david - You can use grid.sortby and grid.ascending to control the initial order. Which strings need to be externalized?
In current source there are lines like:
total records ... ln. 349
prev/next ... lines 362, 363 and arround
view/edit/delete ... lines 243,248,252
file ... ln. 458
Thanks David. You can now do:
Hi mr.freeze,
Any help on my query dated 2010-07-09 (including sum in fields)?
@adsahay - This isn't currently supported. I'll see what I can do to add it.
I think you need to include the table name with man_in_charge:
oh, sorry for such mistake to borther you.
thanks,
I have a datasource which has "sum", like:
I get a key error in grid. How do I fix this?
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?
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.
Okay, I posted a new version. You can now do:
I also fixed a bug with clearing the filters.
Thank's a lot, mr.freeze
thanks, mr.freeze, I'm just look into the source code of webgrid to figture that out. you save my time.
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
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
@frank - try changing the grid.filter_query to limit it to the values you want.
@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.
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?
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.
@yamandu - I sent a patch but in the meantime, you can just handle if your datasource is a list object manually.
Could you give some tips how to use webgrid with datatables and with LOAD?
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.
I then set the grid.css_prefix:
But that also didn't effect anything. I just can't spot what I've missed. Thanks for your help.
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.
I was copying the above layout: {name}-webgrid {component}, when it should have been {name}-webgrid.{component}
All sorted now and working - thanks.
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.
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
Thank You! And now - maybe it is possible to make range filter? For example - from one date to another?
You can override the links to point to the expenses table:
show more comments0
hillmanov 13 years ago
0
dymsza 13 years ago
0
mrfreeze 13 years ago
0
iiit123 14 years ago
0
mrfreeze 14 years ago
0
ammz 14 years ago
0
mrfreeze 14 years ago
0
paulgerrard 14 years ago
0
mrfreeze 14 years ago
0
toan75 14 years ago
0
paulgerrard 14 years ago
0
mrfreeze 14 years ago
0
ammz 14 years ago
0
ammz 14 years ago
db.notas.vendedor.represent = lambda vendedor: auth_user.first_name db.notas.optometrista.represent = lambda optometrista: auth_user.first_name db.notas.armazon1.represent = lambda eyewear: [eyewear.marca,' ',eyewear.modelo,' ',eyewear.caract1] db.notas.lente1.represent = lambda lentes: [lentes.material,' ',lentes.tipo,' ',lentes.tecnoVisual,' ',lentes.tratamiento] db.notas.lc4.represent = lambda lc: [lc.tecnoGradua,' ',lc.marca,' ',lc.duracion,' ',lc.oftalmico,' ',lc.cosmetico] grid = webgrid.WebGrid(crud) grid.datasource = db((db.notas.entregado==False)&(db.notas.vendedor==db.auth_user.id)) grid.pagesize = 20 grid.enabled_rows = ['header','filter','pager','footer'] grid.fields = ['notas.nota','notas.armazon1','notas.lente1','notas.lc4','notas.total','notas.anticipo','notas.pagos','notas.saldo','notas.vendedor','notas.optometrista','notas.created_on'] grid.field_headers = ['Nota','Armazon1','Lente1','L/C1','Total','Antic.','Pagos','Saldo','Vendedor','Optomet.','Del'] grid.totals = ['notas.total','notas.anticipo','notas.pagos','notas.saldo'] grid.crud_function = 'data' grid.action_links = ['view'] grid.action_headers = ['Ver'] grid.filters = ['notas.nota'] grid.filter_items_query = lambda field: (db.notas.entregado==False)&(db.notas.vendedor==db.auth_user.id) grid.view_link= lambda row: A('view', _href= crud.url(f='show_nota', args=[row['id']])) grid.messages.page_info = 'Pagina %(pagenum)s de %(pagecount)s, existen un total de %(total)s registros' grid.messages.view_link = 'Ver' grid.messages.delete_link = 'Eliminar' grid.messages.edit_link = 'Editar' grid.messages.file_link = 'Archivo' grid.messages.previous_page = 'Anterior ' grid.messages.next_page = ' Siguiente' grid.messages.pagesize = ' Num. registros x pagina ' grid.messages.clear_filter = 'Limpiar filtros' grid.sortby = 'notas.nota' crud.settings.controller = 'ventas'
I have this error:Traceback (most recent call last): File "/home/drayco/web2py/gluon/restricted.py", line 188, in restricted exec ccode in environment File "/home/drayco/web2py/applications/opticaluz/controllers/ventas.py", line 489, in
File "/home/drayco/web2py/gluon/globals.py", line 96, in
self._caller = lambda f: f()
File "/home/drayco/web2py/gluon/tools.py", line 2277, in f
return action(*a, **b)
File "/home/drayco/web2py/applications/opticaluz/controllers/ventas.py", line 40, in leer_ventas
return dict(gridnot=grid())
File "/home/drayco/web2py/applications/opticaluz/modules/webgrid.py", line 480, in __call__
r = field.represent(r)
File "/home/drayco/web2py/applications/opticaluz/controllers/ventas.py", line 10, in
db.notas.lc4.represent = lambda lc: [lc.tecnoGradua,' ',lc.marca,' ',lc.duracion,' ',lc.oftalmico,' ',lc.cosmetico]
AttributeError: 'NoneType' object has no attribute 'tecnoGradua'
Any advice? Or how can I avoid this error?0
mrfreeze 14 years ago
0
napoleonmr 14 years ago
0
mrfreeze 14 years ago
0
mrfreeze 14 years ago
0
mrfreeze 14 years ago
0
paulgerrard 14 years ago
0
smg 14 years ago
0
smg 14 years ago
0
mrfreeze 14 years ago
0
ammz 14 years ago
0
hillmanov 14 years ago
0
hillmanov 14 years ago
0
mrfreeze 14 years ago
0
mrfreeze 14 years ago
0
mrfreeze 14 years ago
0
ammz 14 years ago
0
mrfreeze 14 years ago
0
ammz 14 years ago
0
ammz 14 years ago
0
ammz 14 years ago
0
mrfreeze 14 years ago
0
ammz 14 years ago
0
mrfreeze 14 years ago
0
mrfreeze 14 years ago
0
villas 14 years ago
0
yamandu 14 years ago
0
hillmanov 14 years ago
0
mrfreeze 14 years ago
0
hillmanov 14 years ago
0
mrfreeze 14 years ago
0
villas 14 years ago
0
villas 14 years ago
0
villas 14 years ago
0
mrfreeze 14 years ago
0
villas 14 years ago
0
mrfreeze 14 years ago
0
adsahay 14 years ago
0
mrfreeze 14 years ago
0
mrfreeze 14 years ago
0
frank 14 years ago
0
adsahay 14 years ago
0
ammz 14 years ago
0
mrfreeze 14 years ago
0
mrfreeze 14 years ago
0
ammz 14 years ago
0
frank 14 years ago
0
frank 14 years ago
0
frank 14 years ago
0
mrfreeze 14 years ago
0
yamandu 14 years ago
0
yamandu 14 years ago
0
mrfreeze 14 years ago
0
mrfreeze 14 years ago
0
yamandu 14 years ago
0
apardue 14 years ago
0
apardue 14 years ago
0
apardue 14 years ago
0
apardue 14 years ago
0
mrfreeze 14 years ago
0
kroitus 14 years ago
0
mrfreeze 14 years ago