Grid query

[Johann Spies]

In SQL I can do

select doctype, sum(count) number
from wos.za_publications
where pubyear > 1979
group by doctype;

In the web2py shell I can do:

db.define_table('wos_papers_by_dt',
                    Field('pubyear', 'integer'),
                    Field('doctype'),
                    Field('count', 'integer'),
                    migrate = False,
                    rname = 'wos.za_publications')

papers = db.wos_papers_by_dt.count.sum()
query = db.wos_papers_by_dt.pubyear > 1979
l = db(query).select(db.wos_papers_by_dt.doctype,papers, groupby = db.wos_papers_by_dt.doctype)

for i in l:                                                                                   
    print i['_extra']['SUM(wos.za_publications.count)'], i['wos_papers_by_dt']['doctype']

which produces a similar result.

Why is it not working when, using the same model and logic I get an error when I try to put the result in a grid?

za_arts = SQLFORM.grid(query,
                           fields=[db.wos_papers_by_dt.doctype, papers],
                           groupby = db.wos_papers_by_dt.doctype,
                           headers = headers,
                           create = False,
                           editable = False,
                           details = False,
                           deletable = False,
                           searchable = False
                           )

raceback (most recent call last):
  File "/home/js/web2py/gluon/restricted.py", line 227, in restricted
    exec ccode in environment
  File "/home/js/web2py/applications/wos_2015_3/controllers/wos_indicators.py", line 169, in <module>
  File "/home/js/web2py/gluon/globals.py", line 412, in <lambda>
    self._caller = lambda f: f()
  File "/home/js/web2py/applications/wos_2015_3/controllers/wos_indicators.py", line 49, in za_arts_by_doctype
    searchable = False
  File "/home/js/web2py/gluon/sqlhtml.py", line 2183, in grid
    columns = [f for f in fields if f.tablename in tablenames]
AttributeError: 'Expression' object has no attribute 'tablename'

Regards

Johann

--

Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

[Manuele Pesenti]

Il 27/08/15 12:22, Johann Spies ha scritto:

> Why is it not working when, using the same model and logic I get an
> error when I try to put the result in a grid?

+1

I also asked before why expressions are not supported in grid.

to brutally by-pass the exception you can try something like...

papers = db.wos_papers_by_dt.count.sum()
setitem(papers, "tablename", "wos_papers_by_dt")

cheers

Manuele

[Manuele Pesenti]

Il 31/08/15 09:54, Manuele Pesenti ha scritto:

> to brutally by-pass the exception you can try something like...
>
> papers = db.wos_papers_by_dt.count.sum()
> setitem(papers, "tablename", "wos_papers_by_dt")

I apologize... I mean setattr not setitem.

M.

[Johann Spies]

Thanks.  More and more I am using pure sql to get things done, create views and materialized views and just use Web2py to "show" the results.  I find myself spending too much time to figure out how to do some more complicated queries in DAL.  It is just easier to do whatever I want in SQL.

Regards

Johann