sqlform.grid search widget

2,367 views
Skip to first unread message

peter

unread,
Mar 7, 2012, 4:44:00 AM3/7/12
to web2py-users
As I have commented before, sqlform.grid has a particular way of
searching. Its combination of search and 'queries' is very powerful.
However the search does not really work in the way a casual web user
might expect. If entered - Bob Dylan - in the search box and clicked
search, then one get 'invalid query'. I eventually worked out how to
change the search widget, so as I believe this is currently
undocumented I thought I would share it. The semantics I have chosen
for search are that it will return records where any of the specified
fields (fielda and fieldb in the example) contain all of the words in
the search field. i.e if the search term is -Bob Dylan- it will return
records that have both Bob and Dylan in the fieldA or both in fieldb.

Define a search function that creates the form and a function that
does the search

def search_form(self,url):
form = FORM('',

INPUT(_name='search_text',_value=request.get_vars.search_text,
_style='width:200px;',
_id='searchText'),
INPUT(_type='submit',_value=T('Search')),
INPUT(_type='submit',_value=T('Clear'),
_onclick="jQuery('#search_text').val('');"),
_method="GET",_action=url)

return form

def search_query(tableid,search_text,fields):
words= search_text.split(' ') if search_text else []
query=tableid<0#empty query
for field in fields:
new_query=tableid>0
for word in words:
new_query=new_query&field.contains(word)
query=query|new_query
return query

Then in the function that has the sqlform.grid call, before the call
add

search_text=request.get_vars.search_text
query=search_query(db.tablea.id,search_text,
[db.tablea.fielda,db.tablea.fieldb])
...
# the query could now be combined with other queries.

table=SQLFORM.grid(query, search_widget=search_form........)

return dict(table=table)








villas

unread,
Apr 16, 2012, 9:04:45 PM4/16/12
to web...@googlegroups.com
Hi Peter,  
Just to say thank you so much for your posting this, and with this help I did get it working OK.  

@Book Editor.  In the absence of other documentation,  perhaps this example ought to be mentioned in the book?
Thanks, David

Cliff

unread,
Apr 16, 2012, 10:32:49 PM4/16/12
to web...@googlegroups.com
Good one.

You earned a browser bookmark.

Thanks.

Elcimar

unread,
Jul 18, 2012, 10:30:59 AM7/18/12
to web...@googlegroups.com

Works like a charm, but the "Clear" button is working exactly like the "Search" one, without clearing 'search_text' before submit
Is there another method to do that?

peter

unread,
Jul 19, 2012, 11:00:30 AM7/19/12
to web...@googlegroups.com
Thanks for this I had not noticed that clear did not work, I guess I never use it.

The problem is a type in the search_form function

The fifth line should read

id = 'search_text'),

not id='searchText'),


Does anyone know if I can edit the first message in this topic so as to correct it?

Peter

Richard Vézina

unread,
Jul 19, 2012, 11:22:21 AM7/19/12
to web...@googlegroups.com
I think it will solve one of the issue I had with using sqlform.grid in place of sqltable + Datatables. Since Datatables has a pretty simple search/filter and my users knows how to use it than I would not remove this kind of easy search/filter functionality and leave them with only a harder more complex solution, so I stick with sqltable + Datatables even if sqlform.grid is much more interesting...

Thank Peter.

Richard

--
 
 
 

peter

unread,
Jul 20, 2012, 4:29:27 AM7/20/12
to web...@googlegroups.com
I think that there is an argument for incorporating my solution into the standard sqlform grid and having a flag for switching between the query system or my simple text search. If the table is being presented in a web application for the great web public then they are going to expect searches to be 'google like'.

Peter

Richard Vézina

unread,
Jul 20, 2012, 9:07:49 AM7/20/12
to web...@googlegroups.com
Maybe open a issue and summit a patch or your code...


Richard

--
 
 
 

Elcimar

unread,
Jul 20, 2012, 5:12:10 PM7/20/12
to web...@googlegroups.com
it works now =)
thanks dude
about the search query
Found a hack somewhere in the here group telling to delete it directly like:

del grid[0][2]

peter

unread,
Sep 4, 2012, 5:02:22 AM9/4/12
to web...@googlegroups.com
Here is an update on the search widget.
The solution above does not work if the found results spread over several pages. The search is lost on the extra pages.

The solution to this is to change all occurrences of 'search_text' appear in the request with 'keywords'

def search_form(self,url): 
    form = FORM('', 
  
INPUT(_name='search_text',_value=request.get_vars.keywords

               _style='width:200px;', 
               _id='searchText'), 
         INPUT(_type='submit',_value=T('Search')), 
         INPUT(_type='submit',_value=T('Clear'), 
         _onclick="jQuery('#keywords').val('');"), 

         _method="GET",_action=url) 

    return form 

def search_query(tableid,search_text,fields): 
    words= search_text.split(' ') if search_text else [] 
    query=tableid<0#empty query 
    for field in fields: 
        new_query=tableid>0 
        for word in words: 
            new_query=new_query&field.contains(word) 
        query=query|new_query 
    return query 

Then in the function that has the sqlform.grid call, before the call 
add 

    search_text=request.get_vars.keywords 
    query=search_query(db.tablea.id,search_text, 
[db.tablea.fielda,db.tablea.fieldb])  

Peter

peter

unread,
Sep 4, 2012, 5:20:49 AM9/4/12
to web...@googlegroups.com
Sorry the previous email did not contain the final version. Here it is.

def search_form(self,url): 
    form = FORM('', 
  
INPUT(_name='keywords',_value=request.get_vars.keywords
               _style='width:200px;', 
               _id='keywords'), 
Reply all
Reply to author
Forward
0 new messages