I don't think the DAL provides that by default. You have the option of
writing your own pivot function (it's not very complicated, that's the way
I went) or you can use a library like pandas if your production server lets
you install numpy/scipy/pandas. Pandas will give you a lot more data
manipulation functionality but it involves learning yet another tool.
On Monday, March 17, 2014 2:48:21 PM UTC-4, 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.
