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.
