SQLFORM.grid

The simplest of the two is SQLFORM.grid. Here is an example of usage:

  1. @auth.requires_login()
  2. def manage_users():
  3.     grid = SQLFORM.grid(db.auth_user)
  4.     return locals()

which produces the following page:

image

The first argument of SQLFORM.grid can be a table or a query. The grid object will provide access to records matching the query.

Before we dive into the long list of arguments of the grid object we need to understand how it works. The object looks at request.args in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the object links the same function (manage_users in the above case) but passes different request.args.

login required by default for data updates

By default all the URLs generated by the grid are digitally signed and verified. This means one cannot perform certain actions (create, update, delete) without being logged-in. These restrictions can be relaxed:

  1. def manage_users():
  2.     grid = SQLFORM.grid(db.auth_user, user_signature=False)
  3.     return locals()

but we do not recommend it.

Multiple grids per controller function

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via LOAD. To make the default search grid work in more than one LOADed grid, please use a different formname for each one.

Using requests.args safely

Because the controller function that contains the grid may itself manipulate the URL arguments (known in web2py as response.args and response.vars), the grid needs to know which args should be handled by the grid and which not. Here is an example of code that allows one to manage any table:

  1. @auth.requires_login()
  2. def manage():
  3.     table = request.args(0)
  4.     if not table in db.tables(): redirect(URL('error'))
  5.     grid = SQLFORM.grid(db[table], args=request.args[:1])
  6.     return locals()

the args argument of the grid specifies which request.args should be passed along and ignored by the grid. In our case request.args[:1] is the name of the table we want to manage and it is handled by the manage function itself, not by the grid. So, args=request.args[:1] tells the grid to preserve the first URL argument in any links that it generates, appending any grid-specific arguments after that first argument.

SQLFORM.grid signature

The complete signature for the grid is the following:

  1. SQLFORM.grid(
  2.     query,
  3.     fields=None,
  4.     field_id=None,
  5.     left=None,
  6.     headers={},
  7.     orderby=None,
  8.     groupby=None,
  9.     searchable=True,
  10.     sortable=True,
  11.     paginate=20,
  12.     deletable=True,
  13.     editable=True,
  14.     details=True,
  15.     selectable=None,
  16.     create=True,
  17.     csv=True,
  18.     links=None,
  19.     links_in_grid=True,
  20.     upload='<default>',
  21.     args=[],
  22.     user_signature=True,
  23.     maxtextlengths={},
  24.     maxtextlength=20,
  25.     onvalidation=None,
  26.     onfailure=None,
  27.     oncreate=None,
  28.     onupdate=None,
  29.     ondelete=None,
  30.     sorter_icons=(XML('&#x25B2;'), XML('&#x25BC;')),
  31.     ui = 'web2py',
  32.     showbuttontext=True,
  33.     _class="web2py_grid",
  34.     formname='web2py_grid',
  35.     search_widget='default',
  36.     advanced_search=True,
  37.     ignore_rw = False,
  38.     formstyle = None,
  39.     exportclasses = None,
  40.     formargs={},
  41.     createargs={},
  42.     editargs={},
  43.     viewargs={},
  44.     selectable_submit_button='Submit',
  45.     buttons_placement = 'right',
  46.     links_placement = 'right',
  47.     noconfirm=False,
  48.     cache_count=None,
  49.     client_side_delete=False,
  50.     ignore_common_filters=None,
  51.     auto_pagination=True,
  52.     use_cursor=False,
  53.     represent_none=None,
  54.     showblobs=False
  55.     )

  • fields is a list of fields to be fetched from the database. It is also used to determine which fields to be shown in the grid view. However, it doesn’t control what is displayed in the separate form used to edit rows. For that, use the readable and writable attribute of the database fields. For example, in a editable grid, suppress updating of a field like this: before creating the SQLFORM.grid, set

    1. db.my_table.a_field.writable = False
    2. db.my_table.a_field.readable = False

  • field_id must be the field of the table to be used as ID, for example db.mytable.id. This is useful when the grid query is a join of several tables. Any action button on the grid (add record, view, edit, delete) will work over db.mytable.

  • left is an optional left join expressions used to build ...select(left=...).
  • headers is a dictionary that maps ‘tablename.fieldname’ into the corresponding header label, e.g. {'auth_user.email' : 'Email Address'}
  • orderby is used as default ordering for the rows. See Chapter 6 (multiple fields are possible).
  • groupby is used to group the set. Use the same syntax as you were passing in a simple select(groupby=...).
  • searchable, sortable, deletable, editable, details, create determine whether one can search, sort, delete, edit, view details, and create new records respectively. deletable, editable and details are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

  • selectable can be used to call a custom function on multiple records (a checkbox will be inserted for every row) e.g.

    1. selectable = lambda ids : redirect(URL('default', 'mapping_multiple', vars=dict(id=ids)))

    a submit button will be added in the checkboxes column with the label passed in selectable_submit_button; or for multiple action buttons, use a list of tuples:

    1. selectable = [('button label1', lambda...), ('button label2', lambda ...)]

  • paginate sets the max number of rows per page.

  • csv if set to True allows to download the grid in various format (see also exportclasses below).
  • links is used to display new columns which can be links to other pages. The links argument must be a list of dict(header='name', body=lambda row: A(...)) where header is the header of the new column and body is a function that takes a row and returns a value. In the example, the value is a A(...) helper.
  • links_in_grid if set to False, links will only be displayed in the “details” and “edit” page (so, not on the main grid)
  • upload same as SQLFORM’s one. web2py uses the action at that URL to download the file
  • maxtextlength sets the maximum length of text to be displayed for each field value, in the grid view. This value can be overwritten for each field using maxtextlengths, a dictionary of ‘tablename.fieldname’:length e.g. {'auth_user.email' : 50}

  • onvalidation, onfailure, oncreate, onupdate and ondelete are callback functions. All but ondelete take a form object as input, ondelete takes the table and the record id. Because the edit/create form is an SQLFORM which extends FORM, these callbacks are essentially used in the same way as documented in the sections for FORM and SQLFORM. Here is skeleton code:

    1. def myonvalidation(form):
    2.     print "In onvalidation callback"
    3.     print form.vars
    4.     form.errors= True  #this prevents the submission from completing
    6.     #...or to add messages to specific elements on the form
    7.     form.errors.first_name = "Do not name your child after prominent deities"    
    8.     form.errors.last_name = "Last names must start with a letter"
    9.     response.flash = "I don't like your submission" 
    11. def myonfailure(form):
    12.     print 'edit/create validation problem'
    13.     print form.errors
    15. def myoncreate(form):
    16.     print 'create!'
    17.     print form.vars
    19. def myonupdate(form):
    20.     print 'update!'
    21.     print form.vars
    23. def myondelete(table, id):
    24.     print 'delete!'
    25.     print table, id

    onupdate and oncreate callbacks are passed to SQLFORM.process(onsuccess=...).

  • sorter_icons is a list of two strings (or helpers) that will be used to represent the up and down sorting options for each field.

  • ui can be set equal to 'web2py' (default) and will generate web2py friendly class names, can be set equal to 'jquery-ui' and will generate jquery UI friendly class names, but it can also be its own set of class names for the various grid components:

    1. ui = dict(
    2.     widget='',
    3.     header='',
    4.     content='',
    5.     default='',
    6.     cornerall='',
    7.     cornertop='',
    8.     cornerbottom='',
    9.     button='button',
    10.     buttontext='buttontext button',
    11.     buttonadd='icon plus',
    12.     buttonback='icon leftarrow',
    13.     buttonexport='icon downarrow',
    14.     buttondelete='icon trash',
    15.     buttonedit='icon pen',
    16.     buttontable='icon rightarrow',
    17.     buttonview='icon magnifier')

  • search_widget allows to override the default search widget and we refer the reader the source code in gluon/sqlhtml.py for details.

  • advanced_search if set to False, advanced search capabilities are disabled on search widget
  • showbuttontext allows buttons without text (there will effectively be only icons)
  • _class is the class for the grid container.

  • exportclasses takes a dictionary of tuples: by default it’s defined as

    1. dict(
    2.   csv_with_hidden_cols=(ExporterCSV_hidden, 'CSV (hidden cols)', T(...)),
    3.   csv=(ExporterCSV, 'CSV', T(...)),
    4.   xml=(ExporterXML, 'XML', T('XML export of columns shown')),
    5.   html=(ExporterHTML, 'HTML', T('HTML export of visible columns')),
    6.   json=(ExporterJSON, 'JSON', T('JSON export of visible columns')),
    7.   tsv_with_hidden_cols=
    8.       (ExporterTSV, 'TSV (Spreadsheets, hidden cols)', T(...)),
    9.   tsv=(ExporterTSV, 'TSV (Spreadsheets)', T(...)))

    ExporterCSV_hidden, ExporterCSV, ExporterXML, ExporterHTML, ExporterJSON and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like dict(xml=False, html=False) you will disable the xml and html export formats.

  • formargs is passed to all SQLFORM objects used by the grid, while createargs, editargs and viewargs are passed only to the specific create, edit and details SQLFORMs
  • formname, ignore_rw and formstyle are passed to the SQLFORM objects used by the grid for create/update forms.
  • buttons_placement and links_placement both take a parameter (‘right’, ‘left’, ‘both’) that will affect where on the row the buttons (or the links) will be placed
  • noconfirm if set to True do not requires user confirmation upon record delete.
  • cache_count is used to speed up the grid rows counting, it works either caching the selecting query (in this case you pass a tuple as for the cache argument of select, see Chapter 6), or directly providing the number of rows (i.e. an integer or a callable returning the integer count).
  • client_side_delete if set to True requires that the grid gets reloaded on delete on Ajax (i.e. the value of client_side_delete is passed to client_side argument of redirect, see also Chapter 4).
  • ignore_common_filters can be set to True for common filters suppression upon grid db set definition (i.e. while evaluating query). Common filters are described in Chapter 6.
  • auto_pagination if set to True enforce always an ordering over records to avoid pagination issues.
  • use_cursor is specific to Google NoSQL (Datastore) only, if set to True allows use of cursor for pagination.
  • represent_none is an optional value to be used instead of None when showing records in grid/details view.
  • showblobs if set to True tell the grid to show fields of blob type, which by default are not shown.

Virtual fields in SQLFORM.grid and smartgrid

In versions of web2py after 2.6, virtual fields are shown in grids like normal fields: either shown alongside all other fields by default, or by including them in the fields argument. However, virtual fields are not sortable.

In older web2py versions, showing virtual fields in a grid requires use of the links argument. This is still supported for more recent versions. If table db.t1 has a field called t1.vfield which is based on the values of t1.field1 and t1.field2, do this:

  1. grid = SQLFORM.grid(db.t1, ..., fields = [t1.field1, t1.field2, ...],
  2.    links = [dict(header='Virtual Field 1', body=lambda row:row.vfield), ...] )

In all cases, because t1.vfield depends on t1.field1 and t1.field2, these fields must be present in the row. In the example above, this is guaranteed by including t1.field1 and t1.field2 in the fields argument. Alternatively, showing all fields will also work. You can suppress a field from displaying by setting the readable attribute to False.

Note that when defining the virtual field, the lambda function must qualify fields with the table name, but in the links argument, this is not necessary. So for the example above, the virtual field may be defined like:

  1. db.define_table('t1', Field('field1', 'string'),
  2.    Field('field2', 'string'),
  3.    Field.Virtual('vfield', lambda row: row.t1.field1 + row.t1.field2),
  4.    ...)

SQLFORM.smartgrid

A SQLFORM.smartgrid looks a lot like a grid, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

For example consider the following table structure:

  1. db.define_table('parent', Field('name'))
  2. db.define_table('child', Field('name'), Field('parent', 'reference parent'))

With SQLFORM.grid you can list all parents:

  1. SQLFORM.grid(db.parent)

all children:

  1. SQLFORM.grid(db.child)

and all parents and children in one table:

  1. SQLFORM.grid(db.parent, left=db.child.on(db.child.parent==db.parent.id))

With SQLFORM.smartgrid you can put all the data in one object that spawns both tables:

  1. @auth.requires_login()
  2. def manage():
  3.     grid = SQLFORM.smartgrid(db.parent, linked_tables=['child'])
  4.     return locals()

which looks like this:

image

Notice the extra “children” links. One could create the extra links using a regular grid but they would point to a different action. With a smartgrid they are created automatically and handled by the same object.

Also notice that when clicking on the “children” link for a given parent one only gets the list of children for that parent (and that is obvious) but also notice that if one now tried to add a new child, the parent value for the new child is automatically set to the selected parent (displayed in the breadcrumbs associated to the object). The value of this field can be overwritten. We can prevent this by making it readonly:

  1. @auth.requires_login()
  2. def manage():
  3.     db.child.parent.writable = False
  4.     grid = SQLFORM.smartgrid(db.parent, linked_tables=['child'])
  5.     return locals()

If the linked_tables argument is not specified all referencing tables are automatically linked. Anyway, to avoid accidentally exposing data we recommend explicitly listing tables that should be linked.

The following code creates a very powerful management interface for all tables in the system:

  1. @auth.requires_membership('managers')
  2. def manage():
  3.     table = request.args(0) or 'auth_user'
  4.     if not table in db.tables(): redirect(URL('error'))
  5.     grid = SQLFORM.smartgrid(db[table], args=request.args[:1])
  6.     return locals()

smartgrid signature

The smartgrid takes the same arguments as a grid and some more with some caveats:

  • The first argument is a table, not a query
  • There is an extra argument constraints which is a dictionary of ‘tablename’:query which can be used to further restrict access to the records displayed in the ‘tablename’ grid.
  • There is an extra argument linked_tables which is a list of tablenames of tables that should be accessible via the smartgrid.
  • divider allows to specify a character to use in the breadcrumb navigator, breadcrumbs_class will apply the class to the breadcrumb element
  • All the arguments but the table, args, linked_tables, links_in_grid, and user_signature can be dictionaries as explained below.

Consider the previous grid:

  1. grid = SQLFORM.smartgrid(db.parent, linked_tables=['child'])

It allows one to access both a db.parent and a db.child. Apart for navigation controls, for each one table, a smartgrid is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of searchable parameters.

While for a grid we would pass a boolean:

  1. grid = SQLFORM.grid(db.parent, searchable=True)

For a smartgrid we would pass a dictionary of booleans:

  1. grid = SQLFORM.smartgrid(db.parent, linked_tables=['child'],
  2.      searchable= dict(parent=True, child=False))

In this way we made parents searchable but children for each parent not searchable (there should not be that many to need the search widget).

grid and smartgrid access control

grid and smartgrid do not automatically enforce access control like crud does but you can integrate it with auth using explicit permission checking:

  1. grid = SQLFORM.grid(db.auth_user,
  2.      editable = auth.has_membership('managers'),
  3.      deletable = auth.has_membership('managers'))

or

  1. grid = SQLFORM.grid(db.auth_user,
  2.      editable = auth.has_permission('edit', 'auth_user'),
  3.      deletable = auth.has_permission('delete', 'auth_user'))

smartgrid plurals

The smartgrid is the only object in web2py that displays the table name and it needs both the singular and the plural. For example one parent can have one “Child” or many “Children”. Therefore a table object needs to know its own singular and plural names. web2py normally guesses them but you can set them explicitly:

  1. db.define_table('child', ..., singular="Child", plural="Children")

or with:

  1. db.define_table('child', ...)
  2. db.child._singular = "Child"
  3. db.child._plural = "Children"

They should also be internationalized using the T operator.

The plural and singular values are then used by smartgrid to provide correct names for headers and links.