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...
