Hi All,
In the past few days i have been struggling with the problem of generating
a dynamic query.
My goal is to create a reusable model, which gives the possibility to
search all fields in a given table for the presence of a string. The result
will be a list of id's with the records in which the searched string is
present in any field.
My problem always comes down to the simple point that, although i am able
to build a query by examining the table, in the end my constructed query is
a string.
Whenever i try to execute i get an error, as far as i understand caused by
the feeding of a string to the db().
Can anybody give me a hint in the right direction?
Thanks in advance,
Jaap
My sample (*not working*) code:
class SmartSearch(object):
def __init__(self,table):
_start_query = ''
_fields = []
_select = ''
_start_query += '(db.' + str(table) + '.id>0)&'
_select += 'db.' + str(table) + '.id,'
for item in table:
_fields.append(str(item))
if type(item.requires).__name__ == 'IS_IN_DB':
_start_query += '(' + 'db.' + str(item) + '==db.' + str(item
.requires.ktable) + '.id' + ')&'
self._start_query = _start_query[:-1]
self._select = _select
self._fields = _fields
return
def search(self, search_string, search_mode='AND'):
items = search_string.split()
_query = ''
for item in items:
_sub_query = ''
for field in self._fields:
_sub_query += '(db.' + field + '.like("%' + item + '%"))|'
_sub_query = _sub_query[:-1]
_sub_query = '(' + _sub_query + ')'
if search_mode == 'AND':
_query += _sub_query + '&'
else:
_query += _sub_query + '|'
_query = _query[:-1]
totaal_query = self._start_query + '(' + _query + ')'
result = db(totaal_query).select(self._select)
return
--
