hi,
this appears to be an old issue already discussed and marked as
solved:
http://groups.google.com/group/web2py/browse_thread/thread/d7f5e5820176813/4d990c3c7475c48b
http://groups.google.com/group/web2py/browse_thread/thread/f4ef82fd34371863/8e7a741d676cea6e
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-the-order-of-tables-in-from-important
