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.
