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.
