hi Massimo, how about the following:
the _select/select would accept extra parameter 'inner_join' with syntax/semantic analog to 'left', but emit JOIN at the sql level. In case of such a unpleasant query as mine, it would be possible to construct it with inner_join instead of the usual way. My original query would look like this: db(db.first).select(db.first.ALL, db.second.ALL, db.third.ALL, db.fourth.ALL, inner_join=db.second.on(db.first.id==db.second.r12_first_id), left=[db.third.on(db.third.r13_first_id==db.first.id), db.fourth.on(db.fourth.r14_second_id==db.second.id)]) --pawel --- dal.py.orig 2011-04-29 14:19:16.150627000 +0200 +++ dal.py 2011-05-01 19:54:55.316651002 +0200 @@ -717,6 +717,9 @@ def AGGREGATE(self,first,what): return "%s(%s)" % (what,self.expand(first)) + def JOIN(self): + return 'JOIN' + def LEFT_JOIN(self): return 'LEFT JOIN' @@ -969,7 +972,7 @@ def _select(self, query, fields, attributes): for key in set(attributes.keys())-set(('orderby','groupby','limitby', 'required','cache','left', - 'distinct','having')): + 'distinct','having', 'inner_join')): raise SyntaxError, 'invalid select attribute: %s' % key # ## if not fields specified take them all from the requested tables new_fields = [] @@ -1003,6 +1006,7 @@ sql_o = '' sql_s = '' left = attributes.get('left', False) + inner_join = attributes.get('inner_join', False) distinct = attributes.get('distinct', False) groupby = attributes.get('groupby', False) orderby = attributes.get('orderby', False) @@ -1012,6 +1016,15 @@ sql_s += 'DISTINCT' elif distinct: sql_s += 'DISTINCT ON (%s)' % distinct + if inner_join: + ijoin = attributes['inner_join'] + icommand = self.JOIN() + if not isinstance(ijoin, (tuple, list)): + ijoin = [ijoin] + ijoint = [t._tablename for t in ijoin if not isinstance(t,Expression)] + ijoinon = [t for t in ijoin if isinstance(t, Expression)] + ijoinont = [t.first._tablename for t in ijoinon] + iexcluded = [t for t in tablenames if not t in ijoint + ijoinont] if left: join = attributes['left'] command = self.LEFT_JOIN() @@ -1026,14 +1039,26 @@ [tables_to_merge.pop(t) for t in joinont if t in tables_to_merge] important_tablenames = joint + joinont + tables_to_merge.keys() excluded = [t for t in tablenames if not t in important_tablenames ] + if inner_join and not left: + sql_t = ', '.join(iexcluded) + for t in ijoinon: + sql_t += ' %s %s' % (icommand, str(t)) + elif not inner_join and left: sql_t = ', '.join([ t for t in excluded + tables_to_merge.keys()]) if joint: sql_t += ' %s %s' % (command, ','.join([t for t in joint])) - #/patch join+left patch + for t in joinon: + sql_t += ' %s %s' % (command, str(t)) + elif inner_join and left: + sql_t = ','.join([ t for t in excluded + tables_to_merge.keys() if t in iexcluded ]) + for t in ijoinon: + sql_t += ' %s %s' % (icommand, str(t)) + if joint: + sql_t += ' %s %s' % (command, ','.join([t for t in joint])) for t in joinon: sql_t += ' %s %s' % (command, str(t)) else: - sql_t = ', '.join(tablenames) + sql_t = ', '.join(tablenames) if groupby: if isinstance(groupby, (list, tuple)): groupby = xorify(groupby) 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...