CODEWITHSUNDEEP
web2py
Sarah West
Sarah West

Reputation: 2037

SQLFORM.grid() left join output

I'm struggling with web2py and SQLFORM.grid.

What I want to do is the following:

I have three database tables: items, tag and the cross table itemtag.

One item can have several tags and one tag can belong to several items.

What I have so far:

fields = [db.items.id, db.items.title, db.items.rating, db.tag.title]
left = [db.itemtag.on(db.items.id==db.itemtag.item_id), db.tag.on(db.itemtag.tag_id==db.tag.id)]
items = SQLFORM.grid(db.items, left=left, orderby=[db.items.title], fields=fields, create=False, editable=False, details=False, showbuttontext=False, paginate=50, links = [lambda row: A(I(_class="icon-edit"),_href=URL("default","item_edit",args=[row.items.id]))])

Now the SQLFORM.grid produces a grid with all items. But unfortunately if an item have several tags it have several rows in the grid. Like this:

ID | item  | tag
1  | item1 | tag 1
1  | item1 | tag 2
1  | item1 | tag 3
2  | item2 | tag 2
....

Now I'm searching for an groupby function for SQLFORM.grid. What I want is the following:

ID | item  | tag
1  | item1 | tag1, tag2, tag3
2  | item2 | tag

I look forward to any advice.

Upvotes: 0

Views: 2653

Answers (1)

Anthony
Anthony

Reputation: 25536

I don't think you'll be able to do a query like that using the grid (or using the DAL in general). As an alternative, you can use the links argument to generate a custom column that includes the list of tags for each item:

def taglist(row):
    tags = db((db.itemtag.item_id == row.id) &
              (db.itemtag.tag_id == db.tag.id)).select()
    return ', '.join(tag.name for tag in tags)

items = SQLFORM.grid(db.items, orderby=[db.items.title], 
    fields=[db.items.id, db.items.title, db.items.rating],
    create=False, editable=False, details=False,
    showbuttontext=False, paginate=50,
    links = [lambda row: A(I(_class="icon-edit"), _href=URL("default",
                           "item_edit", args=[row.items.id])),
             dict(heading='Tags', body=taglist)])

An item in the links list can be a dict with header and body keys, in which case it will be displayed in a separate column with the specified header.

Note, this method is somewhat inefficient, as it will result in an additional query for each row in the grid in order to retrieve the list of tags for that row.

Another option is to use a virtual field (only very recent versions of web2py display virtual fields in grids):

def taglist(row):
    tags = db((db.itemtag.item_id == row.items.id) &
              (db.itemtag.tag_id == db.tag.id)).select()
    return ', '.join(tag.name for tag in tags)

db.items.tags = Field.Virtual('tags', taglist)

Once you have defined the "tags" virtual field for the db.items table, you can display it in the grid like any other field. Note, the definition of the taglist() function in this case is slightly different than the original above -- when defining virtual fields, it is necessary to refer to the table and field within the row object, so the query includes row.items.id rather than just row.id.

Note, like the links solution, the virtual field alternative also results in a separate query per row of the grid.

The benefit of using a virtual field is that once defined, it can be used in other contexts as well. However, keep in mind that its values will be filled in whenever you select records from the db.items table, so if you don't need the list of tags in other contexts, you might want to define the virtual field conditionally (e.g., only define it in the controller where you need it) in order to avoid unnecessary database queries.

UPDATE: Another option to consider is replacing the many-to-many database model with a list:reference type field in the db.items table to store a list of references to the db.tag table (see example). As long as you set the format attribute of the db.tag table to the tag name (or explicitly set the represent attribute of the list:reference field to display the tag names), you will get a comma-separated list of tag names for that field in the grid.

This method still requires a query per row to get the tag names, but that query doesn't involve a join. The limitation of using a list:reference field, though, is that operations requiring aggregation over tags (e.g., building a tag cloud) can be less efficient because you no longer have fully normalized data.

Note, none of the methods described above allow for searching the tags within the grid. For searchability, the simplest approach is to use a list:string type field in db.items to store the tag. This makes it even more difficult to do operations on tags, though, as there is no longer a tag table with a single record per tag (instead, tags are repeated among the records of the db.items table).

Another option might be to retain the many-to-many model but add a computed field in db.items to store a list of tag names derived from the many-to-many relation. This computed field would display in the grid and be searchable. You would probably also want to add .after_insert and .after_update callbacks to the db.itemtag table that would automatically updated the db.items computed field whenever db.itemtag inserts/updates are made.

Finally, you could create custom grid search functionality. SQLFORM.grid takes a search_widget argument for implementing a custom search widget in the UI, and the searchable argument can be a callable object that takes the search widget keywords submission and produces a subquery to filter the records.

For help with these more advanced options, ask on the Google Group.

Upvotes: 4

Related Questions