hi, somehow I am getting way to many records as a result.
--pawel On Fri, Apr 29, 2011 at 3:53 PM, Massimo Di Pierro <massimo.dipie...@gmail.com> wrote: > Can you please try: > > db(db.first.id.belongs(db()._select(db.second.r12_first_id))) > .select(db.first.ALL,db.second.ALL,db.third.ALL,db.fourth.ALL, > left= [ > db.second.on(db.first.id==db.second.r12_first_id), > db.third.on(db.third.r13_first_id==db.first.id), > db.fourth.on(db.fourth.r14_second_id==db.second.id), > ]) > > > > On Apr 28, 2:22 pm, Pawel Jasinski <pawel.jasin...@gmail.com> wrote: >> hi, >> >> this appears to be an old issue already discussed and marked as >> solved:http://groups.google.com/group/web2py/browse_thread/thread/d7f5e58201...http://groups.google.com/group/web2py/browse_thread/thread/f4ef82fd34... >> but, I got it again :-( >> >> Here is my model: >> >> db.define_table('first', >> Field('f11')) >> db.define_table('second', >> Field('f12'), >> Field('r12_first_id', db.first)) >> db.define_table('third', >> Field('f13'), >> Field('r13_first_id', db.first)) >> db.define_table('fourth', >> Field('f14'), >> Field('r14_second_id', db.second)) >> >> # and the problem: >> db((db.first.id==db.second.r12_first_id) >> ).select(db.first.ALL, db.second.ALL, db.third.ALL, db.fourth.ALL, >> left= >> [db.fourth.on(db.fourth.r14_second_id==db.second.id), >> db.third.on(db.third.r13_first_id==db.first.id)]) >> >> bombs with the postgress error: >> ProgrammingError: invalid reference to FROM-clause entry for table >> "second" >> LINE 1: ...first LEFT JOIN fourth ON (fourth.r14_second_id = >> second.id)... >> ^ >> HINT: There is an entry for table "second", but it cannot be >> referenced from this part of the query. >> >> The inspected sql: >> SELECT first.id, first.f11, second.id, second.f12, >> second.r12_first_id, third.id, third.f13, third.r13_first_id, >> fourth.id, fourth.f14, fourth.r14_second_id >> FROM second, first >> LEFT JOIN fourth ON (fourth.r14_second_id = second.id) >> LEFT JOIN third ON (third.r13_first_id = first.id) >> WHERE (first.id = second.r12_first_id); >> >> The sql which works for me: >> SELECT * >> FROM first JOIN second ON second.r12_first_id=first.id >> LEFT JOIN fourth ON (fourth.r14_second_id = second.id) >> LEFT JOIN third ON (third.r13_first_id = first.id) ; >> >> In my case swapping first and second in sql does not help (I have >> both as part of LEFT JOIN) >> I could use executesql, but is there an easy way to reconnect the >> result of executesql into the rows returned by db(...).select(...)? >> >> Is it only a postgress issue? It worked ok with sqlite. >> >> Am I doing something fundamentally wrong or the old issue is not 100% >> fixed? >> >> Pawel >> >> REF:http://stackoverflow.com/questions/187146/inner-join-outer-join-is-th...
