Also, see here: https://groups.google.com/d/msg/web2py/0dL-SSPauaI/Qcp1qwQgFy4J -- same thing applies to count and any expression used in a select. In this case, the count1 aggregate in the select should be accessible via datasource[count1], which is equivalent to datasource[str(count1)], which results in datasource['COUNT(events.email)'] (or something like that). Note, technically that last version (i.e., the actual SQL expression) could be DB specific, so it's best to use the variable (i.e., datasource[count1]).
Anthony On Monday, October 31, 2011 4:47:43 AM UTC-4, ptressel wrote: > > Hi, Wes! I didn't get back online til after you had left -- sorry! > > On Oct 30, 10:57 am, Wes Hall <wes...@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
