Exporting from a SQLFORM.grid with customized search queries

[Dexter Hadley]

Hi All,

This is my first time posting a question, so thanks to Massimo and they whole community for making web2py.  Its great!

I am trying to export results from a customized full-text search using SQLFORM.grid.  My backend is a Postgres db, and I successfully define "search_widget" and "searchable" functions that are passed to the SQLFORM.grid to do the full-text search.  It will works pretty well on the web app.  However, once I click the export button, SQLFORM.grid apparently recreates the query using the default SQLFORM.build_query and ignores the correct query which I define in searchable.  After poking around in sqlhtml.py, I found this is so because the exporter only conditions on request.vars.keywords before calling  SQLFORM.build_query, and it does not check for callable(searchable) which I think it should do.  In fact, I fixed it by editing sqlhtml.py to force the exporter to condition on (request.vars.keywords and callable(searchable)) before setting up the rows object to export.  The code I added is in bold below (on line 2298 of sqlhtml.py):

                if request.vars.keywords and callable(searchable):

                    try:

                        #the query should be constructed using searchable fields but not virtual fields

                        sfields = reduce(lambda a, b: a + b,

                            [[f for f in t if f.readable and not isinstance(f, Field.Virtual)] for t in tables])

                        dbset = dbset(SQLFORM.build_query(

                            sfields, request.vars.get('keywords', '')))

                        rows = dbset.select(left=left, orderby=orderby,

                                            cacheable=True, *selectable_columns)

                    except Exception, e:

                        response.flash = T('Internal Error')

                        rows = []

                else:

                    rows = dbset.select(left=left, orderby=orderby,

                                        cacheable=True, *selectable_columns)

Is this a bug or is there a better way to do an export of customized search results using SQLFORM.grid?  I'm using the current version of everything (web2py 2.9.11, Postgres 9.3, Python 2.7.8).  Thx again,

dex*

[Dexter Hadley]

CORRECTION:  I got the boolean wrong below...  In fact, I fixed it by editing sqlhtml.py to force the exporter to condition on (request.vars.keywords and not callable(searchable)) before setting up the rows object to export.  That is, if searchable is not defined, then SQLFORM.build_query gets called, else use the query as defined by searchable.  Here is the correct working code:

 if request.vars.keywords and not callable(searchable):

                    try:

                        #the query should be constructed using searchable fields but not virtual fields

                        sfields = reduce(lambda a, b: a + b,

                            [[f for f in t if f.readable and not isinstance(f, Field.Virtual)] for t in tables])

                        dbset = dbset(SQLFORM.build_query(

                            sfields, request.vars.get('keywords', '')))

                        rows = dbset.select(left=left, orderby=orderby,

                                            cacheable=True, *selectable_columns)

                    except Exception, e:

                        response.flash = T('Internal Error')

                        rows = []

                else:

                    rows = dbset.select(left=left, orderby=orderby,

                                        cacheable=True, *selectable_columns)

My question still stands... Is this a bug or is there a better way to do an export of customized search results using SQLFORM.grid?  THx,

dex*

[Massimo Di Pierro]

Hello Dexter, I think your are right. I will review this asap. Meanwhile, would you be able to open an issue with a link to this thread so we can more easily keep track. Thanks!.

Massimo

[Dexter Hadley]

Done: https://code.google.com/p/web2py/issues/detail?id=2006. Glad to help.  Thx,

dex*

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/Td58YKBAaMo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Prasad Muley]

Hi Dexter,

         I am facing the same issue. I've posted a question here

It is throwing an exception because of following the line

      dbset = dbset(SQLFORM.build_query(

                            sfields, request.vars.get('keywords', '')))

I agree with you that SQLFORM.grid should use the custom search query.

I've used your condition i.e added it in sqlhtml.py

if request.vars.keywords and not callable(searchable):

It shows correct record counter on grid. 

But it exports all the record or rand records in exported CSV.

Looks like It is not fetching record according to custom search.

[Prasad Muley]

Hello Massimo and Dexter,

        I've a fix for this issue. I've tested it and attached the diff file. PFA. 

Let me know if I missed anything. 

Btw Can I send pull request?

[Massimo Di Pierro]

Thanks. I will merge this patch!

[Prasad Muley]

Hi Massimo,

           Thanks for reply. I've sent you the pull request on github. Let me know if you get any errors :)

--

Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/Td58YKBAaMo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Thanks and Regards
Prasad M. Muley

[Dexter Hadley]

Thx to both of you!  There is also the google code issue here: https://code.google.com/p/web2py/issues/detail?id=2006

dex*