hi,
short update
> I could use executesql, but is there an easy way to reconnect the
> result of executesql into the rows returned by db(...).select(...)?
As a work around I do the following:
0. modify DAL so select( ) accepts extra parameter to overwrite
generated sql
1. develop and test with sqlite
2. switch to postgres, capture the sql
3. correct sql by hand
4. supply edited sql to db( ).select( ... sql=sql)
modification to dal.py
--- dal.py.orig 2011-04-29 14:19:16.150627000 +0200
+++ dal.py 2011-04-29 13:50:09.080627000 +0200
@@ -969,7 +969,7 @@
def _select(self, query, fields, attributes):
for key in set(attributes.keys())-
set(('orderby','groupby','limitby',
'required','cache','left',
- 'distinct','having')):
+ 'distinct','having',
'sql')):
raise SyntaxError, 'invalid select attribute: %s' % key
# ## if not fields specified take them all from the requested
tables
new_fields = []
@@ -1051,6 +1051,11 @@
if not orderby and tablenames:
sql_o += ' ORDER BY %s' % ', '.join(['%s.%s'%(t,x)
for t in tablenames for x in ((hasattr(self.db[t],'_primarykey') and
self.db[t]._primarykey) or [self.db[t]._id.name])])
# oracle does not support limitby
+ # hack to let manually modify query and use the DAL without
executesql
+ sql = attributes.get('sql',None)
+ if sql:
+ return sql
+ # end hack
return self.select_limitby(sql_s, sql_f, sql_t, sql_w, sql_o,
limitby)
def select_limitby(self, sql_s, sql_f, sql_t, sql_w, sql_o,
limitby):
> Is it only a postgress issue? It worked ok with sqlite.
It bombs with mysql (v5.1.49).
The error message is different, but explanation in mysql forums leads
to the same conclusion
InternalError: (1054, u"Unknown column 'second.id' in 'on clause'")
mysql accepts "FROM (second, first)" which fixes the problem
postgres doesn't
>Am I doing something fundamentally wrong or the old issue is not 100% fixed?
I guess I am the only one who is using combination of a right and 2
left joins in one query :-(
--pawel
