SQLFORM.grid, groupby and sum/count

[James Booth]

Hey guys,

I'm trying to create a SQLFORM.grid that will display the total number of entries for each 'user' in a certain time period.

Basically, my model states that a user can only be in bursary_entries table once per day (This equates to a user swiping their ID card). I would like to be able to find out how many entries are recorded for a specific user per week, month, year, etc. and also filter it by the 'location' column too if possible.

from datetime import datetime
db.define_table('bursary_entries',
                Field('barcode', type='string', requires=[IS_IN_DB_UPPER(db, 'bursary_users.barcode')]),
                Field('entry_time', type='datetime', default=request.now, readable=False, writable=False),
                Field('entry_location', type='string', readable=False, writable=False, requires=[IS_IN_DB(db, 'bursary_locations.bursary_location')]),
                Field('bursary_at_time', type='string', readable=False, writable=False, requires=[IS_IN_DB(db, 'bursary_users.bursary')]),
                format='%(barcode)s'
)

I've tried simply:

query = (db.bursary_entries.entry_time > reportsform.vars.start_date)& \
        (db.bursary_entries.entry_time < reportsform.vars.end_date)

        # Left outer-joins
        joins = [db.bursary_users.on(db.bursary_entries.barcode==db.bursary_users.barcode)]
        grid = SQLFORM.grid(query, left=joins, groupby=db.bursary_entries.barcode)

But obviously that groups the values but doesn't have any kind of count/sum. I've tried a whole host of other things, but they've all fallen flat - Can anyone shed any light on the possibility of this?

[Jurgis Pralgauskis]

Hi, 

I think it echoes my isssues

https://github.com/web2py/web2py/issues/1559 

https://github.com/web2py/web2py/issues/1553

[James Booth]

Hm, so there's no real way to do this at the minute?

The problem is, if I do it via executesql or virtual fields, I can't export it as CSV which is my primary goal.

[Anthony]

On Thursday, January 19, 2017 at 8:02:23 AM UTC-5, James Booth wrote:

Hm, so there's no real way to do this at the minute?

The problem is, if I do it via executesql or virtual fields, I can't export it as CSV which is my primary goal.

You could (a) create a view in the database to represent the data and then define a DAL model based on that view, (b) dynamically load the data into a SQLite memory database and use that with the grid, (c) don't use the grid (i.e., generate the query you need and use the resulting Rows object to generate your own tabular display and CSV export -- the Rows object has an .export_to_csv_file method to facilitate this).

It's rare that there is "no real way" to do something -- it just might be a little more complicated than you'd like. :-)

Anthony

[Anthony]

You could also do something like what is suggested here: https://github.com/web2py/web2py/issues/1553#issuecomment-270406959. Though as noted, the aggregate column will not be sortable, and you'd need to add some code to remove the link in the UI that allows it to be selected for sorting.

Anthony