Hi.
I've come across another subtle problem with web2py's DAL running over IBM
DB2 database.
DB2 requires the left table to immidiatedly preceed the 'left outer join'
keyword.
IOW:
select * from a, b left outer join c on c.a_id=a.id
is incorrect. Correct form is:
select * from b, a left outer join c on c.a_id=a.id
it looks moronish enough, but alas, we have to live with it.
I have modified my local copy of web2py to satisfy this requirement.
Please note that this sample can not be copypasted into web2py's sql.py as
is because of full_table_name wrapper that I added to solve another problem
- schemas support. But replacing 'full_table_name(db, t)' with just 't'
should work just fine.
join = attributes['left']
command = self._db._translator['left join']
if not isinstance(join, (tuple, list)):
join = [join]
joint = [t._tablename for t in join if not isinstance(t,
SQLJoin)]
joinon = [t for t in join if isinstance(t, SQLJoin)]
tables_to_merge={}
[tables_to_merge.update(dict.fromkeys(parse_tablenames(str(t.query)))) for t
in joinon]
joinont = [t.table._tablename for t in joinon]
[tables_to_merge.pop(t) for t in joinont if t in
tables_to_merge]
important_tablenames = joint + joinont + tables_to_merge.keys()
excluded = [t for t in tablenames if not t in
important_tablenames ]
sql_t = ', '.join([full_table_name(self._db, t) for t in
excluded + tables_to_merge.keys()])
if joint:
sql_t += ' %s %s' % (command, ',
'.join([full_table_name(self._db, t) for t in joint]))
for t in joinon:
sql_t += ' %s %s' % (command, str(t))
Regards
Alexey
