Now I understand the problem. You want to pass an expression where a value is
expected. I do not believe we can support this. INSTR does not solve the
problem because it would not be correct to looks for substrings. In fact
paper.authors may contain '|12|13|' and it would incorrectly return that author
'2' is one of authors because it is a substring.
You have to do it in two steps:
person = db(db.person.name='....').select().first()
papers = db.paper.authors.contains(person.id).select()
Massimo
On Jan 21, 2013, at 3:33 PM, pablo.ang...@uam.es wrote:
>
>
> El lunes, 21 de enero de 2013 18:56:07 UTC+1, Massimo Di Pierro escribió:
> 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?
>
> execution of:
>
> db(db.paper.authors.contains(person.id))._select()
>
> gives:
>
> "SELECT person.id, paper.id, paper.title, paper.authors FROM person, paper
> WHERE (paper.authors LIKE '%|person.id|%');"
>
> which I think tries to find those papers whose "authors" field contains the
> literal "person.id", instead of the pairs (paper,person) such that the person
> is one of the authors of the paper.
>
> With the modification I wrote before, it gets converted into:
>
> 'SELECT person.id, paper.id, paper.title, paper.authors FROM person, paper
> WHERE INSTR(paper.authors, person.id);'
>
> with a different meaning.
>
> I can see why using the INSTR operator on MySQL may be better but do we know
> it is faster?
>
> I haven't made any speed test, I'd go for anything that works in sqlite and
> mysql/mariadb.
>
> Thanks for your attention!
>
>
> Massimo
>
> On Monday, 21 January 2013 08:55:40 UTC-6, pablo....@uam.es 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
--
