I have this table
db.define_table('t',
Field('a', type='string'),
Field('b', type='string'),
)
db.t.insert(a='US', b='Washington')
db.t.insert(a='US', b='Rochester')
db.t.insert(a='US', b='Los Angeles')
db.t.insert(a='US', b='Minneapolis')
Now I want to search the items in a grid with a custom search box. I would
like to input in the search box the name of the fields and the value I am
searching for in this way:
a:s b:och b:er
Meaning: find all the record where field a contains "s" and field b
contains 'och' and field b contains 'er'
Copying from previous posts of this group, I have now this:
default.py
def search_form(self,url):
form = FORM('',
INPUT(_name='keywords', _value=request.get_vars.keywords,
_style='width:200px;',
_id='keywords'),
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(search_text):
words= search_text.split(' ') if search_text else []
query = db.t.id<0
queries=[]
queries.append(db.t.id>0)
for word in words:
key,value=word.split(':') if ':' in word else ['',word]
if key=='a':
queries.append(db.t.a.contains(value))
elif key=='b':
queries.append(db.t.b.contains(value))
else:
queries.append(db.t.a.contains(value)|db.t.b.contains(value))
if len(queries) > 0:
query = reduce(lambda a,b:(a&b),queries)
return query
def index():
search_text=request.get_vars.keywords
query=search_query(search_text)
fields=[db.t.a, db.t.b]
table=SQLFORM.grid(query, search_widget=search_form, fields=fields)
return locals()
index.html
{{extend 'layout.html'}}
{{=table}}
Now, if in the search box I type
a:s
The grid is empty and the answer is: *No records found*
the query shown is : ((t.id > 0) AND (t.a LIKE '%s%'))
a:s a:s
The grid returns all the record of US correctly but it writes* **Invalid
query* above the grid
the query shown is : (((t.id > 0) AND (t.a LIKE '%s%')) AND (t.a LIKE
'%s%'))
a:s b:e
same as above, the grid returns all the record correctly but it writes *Invalid
query* above the grid
the query shown is : (((t.id > 0) AND (t.a LIKE '%s%')) AND (t.b LIKE
'%e%'))
What am I doing wrong?
--
