Massimo,
Yes, it works. The DAL now generates the following SQL, which does bring
back the results I was expecting:
<Query (((interest.spectator = 2) AND (interest.genre = genre.id)) AND (CAST
(movie.genres AS CHAR(512)) LIKE ('%|' || genre.id || '|%')))>
I should mention that when I tested this model with the default SQLite
driver, I got an error ticket. It is probably unrelated to this commit, but
here it is just in case:
<class 'sqlite3.OperationalError'> no such function: CONCAT
Traceback (most recent call last):
File "/home/ricardo/src/web2py/gluon/restricted.py", line 212, in
restricted
exec ccode in environment
File
"/home/ricardo/src/web2py/applications/welcome/controllers/default.py",
line 85, in <module>
File "/home/ricardo/src/web2py/gluon/globals.py", line 194, in <lambda>
self._caller = lambda f: f()
File
"/home/ricardo/src/web2py/applications/welcome/controllers/default.py",
line 29, in myinterestingmovies
rows = db(query).select(db.movie.name, distinct=True)
File "/home/ricardo/src/web2py/gluon/dal.py", line 9865, in select
return adapter.select(self.query,fields,attributes)
File "/home/ricardo/src/web2py/gluon/dal.py", line 2226, in select
return super(SQLiteAdapter, self).select(query, fields, attributes)
File "/home/ricardo/src/web2py/gluon/dal.py", line 1684, in select
return self._select_aux(sql,fields,attributes)
File "/home/ricardo/src/web2py/gluon/dal.py", line 1649, in _select_aux
self.execute(sql)
File "/home/ricardo/src/web2py/gluon/dal.py", line 1762, in execute
return self.log_execute(*a, **b)
File "/home/ricardo/src/web2py/gluon/dal.py", line 1756, in log_execute
ret = self.cursor.execute(*a, **b)
OperationalError: no such function: CONCAT
thanks for your time. best regards -Ricardo
On Monday, April 1, 2013 9:31:59 AM UTC-5, 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.
