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.
