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
<[email protected]> 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 <[email protected]> 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...
