Attached is my updated version of sqlhtml.py.
It got a little more complicated than I initially thought because of
the nature of smartgrid and needing to be concerned about related
tables as well.
Here is the controller code I'm using to implement the custom search
widget feature.
def contactSearch(self):
# Build drop-down list for districts
userId = auth.user.id
districtQuery =
db(db.userDistrict.userId==userId)._select(db.userDistrict.districtId)
userDistricts =
db(db.district.id.belongs(districtQuery)).select(orderby=db.district.manager)
options = [OPTION(userDistricts[i].manager,
_value=str(userDistricts[i].id)) for i in
range(len(userDistricts))]
optionsAdded=options[:]
optionsAdded.insert(0, OPTION('- All Districts -', _value='0'))
form = FORM('',
SELECT(_name='district', _id='district',
_style='width:150px;',
value=request.get_vars.district,
*optionsAdded),
INPUT(_name='searchText',_value=request.get_vars.searchText,
_style='width:200px;',
_id='searchText'),
INPUT(_type='submit',_value=T('Search')),
INPUT(_type='submit',_value=T('Clear'),
_onclick="jQuery('#district').val('');jQuery('#searchText').val('');"),
_id='contactSearch')
return form
@auth.requires_permission('select', db.contact)
def contacts():
response.view='list.html'
response.title = db.contact._plural
columns = ['contact.company', 'contact.firstName',
'contact.lastName', 'contact.city',
'contact.state', 'contactTag.tagId',
'contactPriceTemplate.priceTemplateId']
orderby = dict(contact=[db.contact.company, db.contact.lastName,
db.contact.firstName])
queries = []
constraints = None
# Get filters
searchText = request.get_vars.searchText
districtId = request.get_vars.district
try:
districtId = int(districtId)
except:
districtId = 0
# Build query
if searchText and searchText .strip() != '':
queries.append(db.contact.city.contains(searchText))
if districtId and districtId > 0:
queries.append(db.contact.districtId==districtId)
if len(queries) > 0:
query = reduce(lambda a,b:(a&b),queries)
constraints={'contact':query}
db.contactTag._plural = 'Tags'
db.contactPriceTemplate._plural = 'Price Lists'
db.contactPriceTemplate.contactId.writable = False
db.contactPriceTemplate.contactId.readable = False
# Setup search forms
searchForms = dict(contact=contactSearch,
contactPriceTemplate=None,
contactTag=None)
grid = SQLFORM.smartgrid(db.contact, columns=columns,
constraints=constraints,
details=False, orderby=orderby,
csv=False, search_widget=searchForms,
paginate=15, maxtextlength=45)
return dict(grid=grid)
This is all working rather nicely for the main table used in
SQLFORM.smartgrid. However, I found problems with the search
functionality on related tables that I suspect to be a bug in the
smartgrid search, but have not yet proven. I will work on this
further to verify.
Let me know if you want more information before considering this
change to the code. This feature is very important to me and I'm
willing to work with it further if you'd like.
-Jim
On 11/3/2011 8:00 PM, Massimo Di Pierro wrote:
Please make sure you apply your change to the latest sqlhtml.py (I
modified it this afternoon) and send me the whole modified file or a
patch file.
massimo
On Nov 3, 4:26 pm, Jim Steil<[email protected]> wrote:
Ok - no replies so I went about this myself and am trying to come up
with a solution.
First off, the reasons why the default search doesn't work for me.
-As in the example below, sometimes I want to provide a custom
drop-down
for users to select from to limit the records displayed
-Date values are not handled by the default search
-Sometimes want an auto-complete field available in my search criteria
So, to make this work I looked at the code in SQLFORM.grid related to
the searching. What I want to do is remove the default searching
altogether, including the query-building wizard (which I believe in
other environments is very powerful).
In looking at this code it occurred to me that I should be able to
replace the default search_widget with my own form definition and then
look at the values returned to my controller and build the necessary
query to pass to SQLFORM.smartgrid. What I found was that this work
very easily. The only problem is that I want the default search to not
appear while my custom search form does appear. The problem is that
when I pass searchable=False to my grid that neither my custom search
nor the default search mechanism appear. When I pass searchable=True,
both of them appear. I'd like an option where I can pass my own search
form via search_widget but not display the default search fields.
I believe this could be done relatively easily by adding the following
to SQLFORM.grid:
form = FORM(
search_widget and search_widget([f for f in table if
f.readable]) or '',
_method="GET",_action=url())
console.append(form)
Starting after line 1649 in the latest (updated this morning)
sqlhtml.py. Right after the line that reads: subquery = None
Using the following SQLFORM.smartgrid call:
grid = SQLFORM.smartgrid(db.assetType, constraints=constraints,
columns=columns,
details=False, orderby=orderby,
csv=False, search_widget=searchForm,
searchable=None,
paginate=15, maxtextlength=45)
searchForm is defined in the same controller as:
def searchForm(self):
form = FORM('',
LABEL('Name:', _for='asset_name'),
INPUT(_name='asset_name',_value=request.get_vars.asset_name,
_id='web2py_asset_name'),
INPUT(_type='submit',_value=T('Search')),
INPUT(_type='submit',_value=T('Clear'),
_onclick="jQuery('#web2py_asset_name').val('');"),
_method="GET",_action='',
_id='assetSearchForm')
return form
Now I can provide my own custom search widget providing I handle the
query building myself. Thoughts? Warning - my code works for my
situation but is not very pretty.
I would like this to be considered for a patch. Any further input
would
be greatly appreciated.
-Jim
On 11/1/2011 8:38 AM, Jim Steil wrote:
Hi
I'm using the SQLFORM.grid extensively in a rewrite of my old
TurboGears application. A common thing that I provided in my previous
version of the application was to present to the user different
filtering options for lists. Here is a screen shot of it:
For my needs, the new SQLFORM.grid search_widget doesn't quite fit.
I'd like to provide my own search widget. In looking through
sqlhtml.py at the search_widget code it would appear that I could
provide my own widget. I'm just wondering if anyone else has done
this yet and has any code or advice they'd like to share.
Thanks for any input!
-Jim
