What database server are you using? Some have SQL extensions which do
"cross tabs" and "cubes", which would mean writing the SQL and doing
executesql.
On Tuesday, 18 March 2014 05:48:21 UTC+11, Michael Beller wrote:
>
> 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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
