El lunes, 21 de enero de 2013 22:47:41 UTC+1, Massimo Di Pierro escribió:
>
> 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.
>
ok, but we can use CONCAT to append and prepend '|', that's more standard
that instr.
>
> You have to do it in two steps:
>
> person = db(db.person.name='....').select().first()
> papers = db.paper.authors.contains(person.id).select()
>
But it's not a one-time query. I'm working in something like appadmin,
tweaking smart_query and SQLFORM.grid to help the user build compund
querys. I'd rather normalize the database.
BTW, I will come later with some ideas/questions.
Thanks again
>
> Massimo
>
>
> On Jan 21, 2013, at 3:33 PM, pablo....@uam.es <javascript:> 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
>>>
>>
>
--
