Oh, Massimo, actually I was wrong here again. Sorry for second time
reporting same issue while still not being a bug.
However, I still catched one, related specifically to Oracle.
As it happened, using LEFT JOIN here is more correct as I need empty groups
too. But Oracle chockes with error ORA-00905 if there is a keyword 'AS' in
sql. So the following patch made it work for me:
--- sql.py 2010-03-11 18:18:38.000000000 +0300
+++ sql.py 2010-04-16 10:04:34.087553445 +0400
@@ -1586,7 +1590,10 @@
def __str__(self):
if self.get('_ot', None):
- return '%s AS %s' % (self._ot, self._tablename)
+ if self._db._dbname == 'oracle':
+ return '%s %s' % (self._ot, self._tablename)
+ else:
+ return '%s AS %s' % (self._ot, self._tablename)
return self._tablename
def with_alias(self, alias):
Regards
Alexey
On Thu, Apr 15, 2010 at 5:40 PM, mdipierro <[email protected]> wrote:
> Alias was not designed to let you rename tables in arbitrary cases. It
> was designed to prevent naming conflicts when you left join the same
> table multiple times. It works in this second case. So this should
> work
>
> db(db.TEST.id>0). select(TEST.ALL,
> b.id.count(),left=b.on(b.top_id==TEST.id))
>
> I agree this is not completely equivalent to your query.
>
> Massimo
>
> On Apr 15, 3:07 am, Alexey Nezhdanov <[email protected]> wrote:
> > Ok, I bumped into same problem again.
> > But this time I actually DO use same table twice.
> >
> > So, the task: there is a table that MAY refer to itself.
> > In my application monitors network tree for disconnections (is just pings
> > hosts). Some alerts 'come out of the blue' and some are caused by these -
> > i.e. we have cascade effects:
> > monitoring_app--A--B--C
> > if A is the server and B fails - then there will be two alerts: 'B
> > inaccessible' and 'C inaccessible' but record for node C will actually
> refer
> > to record
> > for node B because C probably is ok, we just can't check it.
> >
> > I need to fetch all 'out of the blue' alert records AND number of
> associated
> > alerts for each.
> > correct SQL will be something like
> > select a.id, count(b.id) from TEST a, TEST b where b.top_id=a.id and
> a.id=0
> > group by a.id
> > However web2py losts table names in the process causing DB error.
> > (stripped everything down so below is all that is needed to reproduce the
> > problem. groupby is not needed so stripped out too)
> > =======models/db.py=========
> > db = DAL('sqlite://storage.sqlite')
> > db.define_table('TEST',Field('top_id','integer'))
> > ======controllers/default.py=====
> > def index():
> > a=db.TEST.with_alias('a')
> > b=db.TEST.with_alias('b')
> > print db((a.id==0)&(b.top_id==a.id))._select(a.ALL, b.id.count())
> > print db((a.id==0)&(b.top_id==a.id)). select(a.ALL, b.id.count())
> > return dict(message=T('Hello World'))
> > ==========================
> >
> > The question is - is there a workaround (except obvious - two TWO sql
> > queries) or may be I am just not using aliases in the correct way again?
> >
> > Regards
> > Alexey.
>
>
> --
> To unsubscribe, reply using "remove me" as the subject.
>
