Hi, Wes! I didn't get back online til after you had left -- sorry! On Oct 30, 10:57 am, Wes Hall <wesh...@gmail.com> wrote: > I am trying to show a grouped count in a powertable. How do I > reference the count's storage object? > > <snippet> > def event_list(): > class Virtual(object): > @virtualsettings(label='Count') > def email_count(self): > return ??????????? # What do I need to reference? > > report_id = int(request.args[0]) > > count1 = db.events.email.count() > datasource = db( > (db.events.report_id==report_id) & > (db.events.event_type=='event1') > ).select( > db.events.id, > count1, > db.events.email, > db.events.name, > db.users.first_name, > db.users.last_name, > left=db.users.on( > db.events.email==db.users.email), > groupby=db.events.email) > > table = plugins.powerTable > table.datasource=datasource > table.headers = 'labels' > table.virtualfields = Virtual() > table.keycolumn = 'events.id' > table.columns = ['virtual.email_count','events.email'] > > ... > </snippet> > > This is my first web2py project and second python project, so if it is > a lack of basic understanding, pointing me towards helpful resources > would also be appreciated.
I'm guessing you read... http://web2py.com/book/default/chapter/06?search=count#Grouping-and-Counting "Notice the count operator (which is built-in) is used as a field. The only issue here is in how to retrieve the information. Each row clearly contains a person and the count, but the count is not a field of a person nor is it a table. So where does it go? It goes into the storage object representing the record with a key equal to the query expression itself." ...and were wondering what that key looks like. If I'm guessing correctly, then it may be "COUNT(events.email)". Here's a way to find out -- start up the Web2py command line, do the query that uses the count field, and look at the column heading names in the result. Here's an example of doing this on our database. (I'm using actual table and field names, to avoid introducing typos by changing them. I'm showing this with a plain Python shell since that's cleaner.) $ python web2py.py -N -M -P -S eden (Replace "eden" with your application directory. >>> count=db.gis_location.id.count() >>> rows=db(db.gis_location.id>0).select(db.gis_location.level,count,groupby=db. gis_location.level) >>> print rows gis_location.level,COUNT(gis_location.id) L0,246 There is only one value of "level" in the table here, so the result of the query contains only one Row in the returned Rows object. Get one Row out, and then look up the value for key "COUNT(gis_location.id)": >>> row=rows[0] >>> row['COUNT(gis_location.id)'] 246 Just FYI (and others please correct me if I'm wrong): If there is data from more than one table, the Row class nests each table's data in another layer of Row objects, one with the data from each table. Anything not in a table is put in a Row indexed by "_extra". That is: >>> row.keys() ['gis_location', '_extra'] >>> row['_extra'] <Row {'COUNT(gis_location.id)': 246}> >>> row['gis_location'] <Row {'level': 'L0'}> So...you might try indexing by "COUNT(events.email)" rather than "virtual.email_count" in table.columns. If you try the query at the Web2py prompt, and print the result, is that what it has in its column names? -- Pat
