Creating a sums value from a column on an SQLFORM.grid

20 views
Skip to first unread message

mostwanted

unread,
Apr 7, 2022, 4:29:37 AMApr 7
to web2py-users
Hi guys, I was wondering if i could create a sums column on an SQLFORM.grid so that every search that's made produces a sums value, something like this:

def payments():
    grid = SQLFORM.grid(db.payments, args=[db.payments])
    if grid.process().accepted:
        query=("#Search queries of the SQLFORMgrid")
        amount = db(query).select((db.payments.amount).sum().with_alias('total'))
    return locals()

Whats possible and whats not?

Regards;

Jim S

unread,
Apr 7, 2022, 8:26:48 AMApr 7
to web2py-users
It looks like there is support for groupby in SQLFORM.grid which is what you'd need to build the proper SQL statement.

I haven't tried this with web2py.  If I were, I'd try adding a groupby clause and a list of fields that include your sum field.  Something like this (not tested)

payments_sum_field = db.payments.amount.sum()
SQLFORM.grid(db.payments, fields=[db.payments.field_1, db.payments.field_2, payments_sum_field], groupby=[db.payments.field_1, db.payments.field_2])


FWIW - I just submitted a PR for py4web to do exactly what you're talking about.  Looking at the code in web2py for groupby I see that I can improve what I'd submitted so far.

-Jim

mostwanted

unread,
Apr 8, 2022, 2:09:57 AMApr 8
to web2py-users
I'm trying this code below & i'm getting <type 'exceptions.AttributeError'> 'Expression' object has no attribute 'tablename'    ERROR, whats wrong with my code? or What am i missing??

def payments():
    payments_sum_field = db.payments.amount.sum().with_alias('total')
    grid_table=SQLFORM.grid(db.payments, fields=[db.payments.client, db.payments.amount, db.payments.recorded,db.payments.recorded_on, payments_sum_field])
    return locals()

Jim S

unread,
Apr 8, 2022, 8:05:34 AMApr 8
to web2py-users
I did a bunch of work in py4web yesterday getting this to work.  I was getting a similar error - I updated the py4web 'grid' code to fix it.  I've never used an Expression like this in web2py before so I can't say for sure that it is supported.

A couple other things I noticed.
1. You don't have a groupby specified on your .grid call.  With your code, if you get past your error, will only return 1 row with the total.
2. You're asking for the payments.amount field to be displayed as well as the payments_sum_field. It doesn't make sense to me that you'd want to display the individual amount along with the sum. I think you want something more like this:

def payments():
    payments_sum_field = db.payments.amount.sum().with_alias('total')
    grid_table=SQLFORM.grid(db.payments, fields=[db.payments.client, db.payments.recorded_on, payments_sum_field], groupby=[db.payments.client, db.payments.recorded_on])
    return locals()

With this you'll get one 1 record for each client for each day with their total payments received.

But, you'll still have to find how to fix your Expression object has no attribute tablename issue.

-Jim
Reply all
Reply to author
Forward
0 new messages