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

Reply via email to