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.