@ricardo: this is done under the assumption that contains() takes a field
.... it's nowhere documented in the book that this should work.....
contains() (just a few moments before the latest commit by Massimo) was
supposed to take a "fixed" value, not a column.....
On Monday, April 1, 2013 4:31:59 PM UTC+2, Massimo Di Pierro wrote:
>
> Fixed in trunk. Please check it out.
>
> On Monday, 1 April 2013 08:59:20 UTC-5, Ricardo Cárdenas wrote:
>>
>> Hi, I think I've run into this problem too (Web2py 2.4.5-stable,
>> PostgreSQL 9.1.8.).
>>
>> I have a list of movies; each may be tagged with one or more genres. Each
>> user in the system may show interest in one or more genres:
>>
>> db.define_table('genre', Field('name'), format='%(name)s')
>> db.define_table('movie', Field('name'), Field('genres', 'list:reference
>> genre'))
>> db.define_table('interest',
>> Field('spectator', 'reference auth_user'), Field('genre', 'reference
>> genre'))
>>
>> I would have expected this query to return all movies tagged with genres
>> the user is interested in:
>>
>> def myinterestingmovies():
>> query = (
>> (db.interest.spectator == auth.user.id) &
>> (db.interest.genre == db.genre.id) &
>> (db.movie.genres.contains(db.genre.id)))
>> rows = db(query).select(db.movie.name, distinct=True)
>> return locals()
>>
>> But no rows are returned. The SQL generated by the DAL is:
>>
>> SELECT DISTINCT movie.name FROM genre, movie, interest WHERE
>> (((interest.spectator
>> = 2) AND (interest.genre = genre.id)) AND (CAST(movie.genres AS CHAR(512
>> )) LIKE '%|genre.id|%'));
>>
>> The column name is inserted within the LIKE literal. In PostgreSQL, the
>> following does yield the expected list of movies:
>>
>> SELECT DISTINCT movie.name FROM interest, genre, movie WHERE
>> (((interest.spectator
>> = 1) AND (interest.genre = genre.id)) AND (CAST(movie.genres AS CHAR(512
>> )) LIKE '%|' || genre.id || '|%'));
>>
>> Would that be a good solution to rewrite CONTAINS, or is there a better
>> way to rewrite my query? best regards -Ricardo
>>
>>
>> On Monday, January 21, 2013 9:55:40 AM UTC-5, [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
>>>
>>
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
