I'm trying to create a query that produces a pivot table. I have a Project
and Project Status table. Each Project has a Status reference field to a
Project Status and a Manager reference field to an Auth User.
db.define_table('t_project_status',
Field('f_name', requires=IS_NOT_EMPTY(), label=T('Name')),
auth.signature)
db.define_table('t_project',
Field('f_name', requires=IS_NOT_EMPTY(), label=T('Name')),
Field('f_description', 'text', readable=False, label=T('Description')),
Field('f_manager', 'reference auth_user', label=T('Manager')),
Field('f_status', 'reference t_project_status', label=T('Status')),
auth.signature)
I would like a table that produces a row for every Manager and a column for
every Status (which I can then display as a Bar Chart using Google Charts).
This query comes close to at least producing a normalized list, i.e., one
row for each combination of Manager/Status in the Project table (but not an
entry for a Status that doesn't exist for a particular Manager, I can't
figure out how to add a 2nd constraint to the 'left' clause):
joinquery = ((db.t_project.f_status == db.t_project_status.id) &
(db.t_project.f_manager == db.auth_user.id))
data = db(joinquery).select(
db.auth_user.last_name,
db.t_project.f_status,
db.t_project_status.f_name,
db.t_project_status.id.count(),
groupby=(db.auth_user.id|db.t_project_status.id),
orderby=(db.auth_user.last_name|db.t_project_status.id),
left=db.auth_user.on(db.t_project.f_manager == db.auth_user.id),
)
I've started to look at itertools.groupby but was trying to find a DAL
solution to create the pivot table.
I'm then using this code to create a table for Google Charts using the
Google Charts Plugin but will need to modify once I get the pivot table
working:
datalist = []
datalist.append(['Manager','Status','Count'])
for row in data:
datalist.append([row.auth_user.last_name or 'None',
row.t_project.f_status or 'None', int(row[db.t_project_status.id.count()])])
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
