*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
What error do you get?
ups, ok this is it
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:
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?
show more comments0
hillmanov 13 years ago
0
dymsza 13 years ago
0
mrfreeze 13 years ago
def add_mail_link(tablerow,rowtype,rowdata): if rowtype == 'datarow': mail_link = A(...) tablerow.components.insert(2, mail_link) grid.row_created = add_mail_link0
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
grid.datasource = db((db.stories.priority_id==db.storypriority.id) & (db.stories.assigned_id==db.auth_user.id) & \ (db.stories.status_id==db.storystatus.id) & (db.stories.app_id==db.applications.id) & \ (db.applications.org_id==session.org_id)) grid.filter_items_query = lambda field: (db.stories.app_id==db.applications.id) & (db.applications.org_id==session.org_id)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
db.define_table("pagos", Field("nota", 'integer', requires=IS_NOT_EMPTY(error_message='No puede estar vacio'), label='No. de Nota'), Field("pago", "double", requires=IS_NOT_EMPTY(error_message='No puede estar vacio')), Field("saldo", "double", default=0,writable=False), Field("vendedor",db.vendedores, requires=IS_IN_DB(vendedores,'vendedores.id','%(names)s %(apelPater)s %(apelMater)s')), Field('entregado','boolean', default=False), Field('entregadoEl', 'datetime', default=request.now, label='Entregado el'), Field("observaciones","string"))@auth.requires_membership('Capturista') def lee_entregas(): Capturista = db(db.capturistas.creadoPor==auth.user.id).select()[0] db.pagos.vendedor.represent = lambda vendedores: vendedores.names grid = webgrid.WebGrid(crud) grid.datasource = db((db.pagos.vendedor==db.vendedores.id)&(db.vendedores.regional_id==Capturista.regional_id)) grid.pagesize = 50 grid.enabled_rows = ['header','filter','pager','footer'] grid.fields = ['pagos.nota','pagos.pago','pagos.saldo','pagos.entregadoEl','pagos.vendedor'] grid.field_headers = ['Nota','Pago','Saldo','Entregado','Vendedor'] grid.crud_function = 'data' # Set the function where crud will be exposed. grid.action_links = ['view'] grid.action_headers = ['Ver'] grid.filters = ['pagos.nota'] grid.filter_items_query = lambda field: (db.pagos.vendedor==db.vendedores.id)&(db.vendedores.regional_id==Capturista.regional_id) grid.view_link= lambda row: A('view', _href= crud.url(f='show_entrega', args=[row['pagos']['id']])) crud.settings.controller = 'entregas' return dict(gridpag=grid())0
mrfreeze 14 years ago
0
ammz 14 years ago
Traceback (most recent call last): File "/home/drayco/web2py/gluon/restricted.py", line 178, in restricted exec ccode in environment File "/home/drayco/web2py/applications/seccion36_3_1/controllers/entregas.py", line 160, in
File "/home/drayco/web2py/gluon/globals.py", line 96, in
self._caller = lambda f: f()
File "/home/drayco/web2py/gluon/tools.py", line 2221, in f
return action(*a, **b)
File "/home/drayco/web2py/applications/seccion36_3_1/controllers/entregas.py", line 158, in lee_entregas
return dict(gridpag=grid())
File "/home/drayco/web2py/applications/seccion36_3_1/modules/webgrid.py", line 441, in __call__
tr.components.append(TD(self.view_link(row),
File "/home/drayco/web2py/applications/seccion36_3_1/controllers/entregas.py", line 144, in
grid.view_link= lambda row: A('view', _href= crud.url(f='show_entrega', args=[row['pagos']['id']]))
File "/home/drayco/web2py/gluon/sql.py", line 661, in __getitem__
return dict.__getitem__(self, key)
KeyError: 'pagos
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
db.define_table('allstats', Field('pdate','datetime'), Field('class','string',length=45), Field('instance','string',length=45), Field('parameter','string',length=45), Field('hostname','string',length=45), Field('lastv','double'), Field('minv','double'), Field('maxv','double'), Field('avgv','double'), Field('cntv','integer'), Field('peaktime','datetime'), Field('sumv','integer'), Field('sitelocation','string',length=30), migrate=False) webgrid = local_import('webgrid')Controller snippet:grid = webgrid.WebGrid(crud) grid.datasource = db(db.allstats.id<30000) # Set grid.crud_function = 'index' # Set the function where crud will be exposed. grid.fields = ['allstats.pdate','allstats.parameter','allstats.peaktime'] grid.pagesize = 100 return dict(grid=grid()) #notice the ()Wireshark capture from client: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
grid.messages.page_info = 'Page %(pagenum)s of %(pagecount)s Total %(total)s' grid.messages.view_link = 'View' grid.messages.delete_link = 'Delete' grid.messages.edit_link = 'Edit' grid.messages.file_link = 'File' grid.messages.previous_page = ' Previous' grid.messages.next_page = 'Next '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