@niphlod - indeed, the use of expressions rather than values is not
mentioned specifically in Ch.
6<http://web2py.com/books/default/chapter/29/06#list:<type>,-and-contains>,
though it is also not discounted. The added functionality would seem useful
in many situations, while retaining the use of fixed values as well...
thanks -Ricardo
On Monday, April 1, 2013 9:38:50 AM UTC-5, Niphlod wrote:
>
> @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.
