Before we attempt to fix this. It works for me as it is with sqlite. Why does doesn't it wirk with mysql? MySQL has a LIKE operator. Wat error do you get?
I can see why using the INSTR operator on MySQL may be better but do we know it is faster? Massimo On Monday, 21 January 2013 08:55:40 UTC-6, [email protected] wrote: > > Hello: > I was getting incorrect SQL from statements like: > > db(db.paper.authors.contains(person.id)).select() > > where > > db.define_table('paper', > Field('title'), > Field('authors', 'list:reference person'), > ) > > > so I rewrote dal.MySQLAdapter.CONTAINS from: > > def CONTAINS(self, first, second): > if first.type in ('string', 'text'): > key = '%'+str(second).replace('%','%%')+'%' > elif first.type.startswith('list:'): > key = '%|'+str(second).replace('|','||').replace('%','%%')+'|%' > return '(%s LIKE %s)' % > (self.expand(first),self.expand(key,'string')) > > into: > > def CONTAINS(self, first, second): > if first.type in ('string', 'text'): > key = '%'+str(second).replace('%','%%')+'%' > elif first.type.startswith('list:reference'): > return 'INSTR(%s, %s)'%(first, str(second)) > elif first.type.startswith('list:'): > key = '%|'+str(second).replace('|','||').replace('%','%%')+'|%' > return '(%s LIKE %s)' % > (self.expand(first),self.expand(key,'string')) > > now the above query works, but that's in MySQL, not SQLite, which > apparently does not have a INSTR operation, despite the fact that it is > advertised! > > https://www.sqlite.org/lang_corefunc.html > > Can anybody think of a solution that works in more databases > --
