postgress outer join

[Pawel Jasinski]

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

[Pawel Jasinski]

hi,

short update

> I could use executesql, but is there an easy way to reconnect the
> result of executesql into the rows returned by db(...).select(...)?

As a work around I do the following:
0. modify DAL so select( ) accepts extra parameter to overwrite
generated sql
1. develop and test with sqlite
2. switch to postgres, capture the sql
3. correct sql by hand
4. supply edited sql to db( ).select( ... sql=sql)

modification to dal.py
--- dal.py.orig 2011-04-29 14:19:16.150627000 +0200
+++ dal.py 2011-04-29 13:50:09.080627000 +0200
@@ -969,7 +969,7 @@
def _select(self, query, fields, attributes):
for key in set(attributes.keys())-
set(('orderby','groupby','limitby',

'required','cache','left',
- 'distinct','having')):
+ 'distinct','having',
'sql')):
raise SyntaxError, 'invalid select attribute: %s' % key
# ## if not fields specified take them all from the requested
tables
new_fields = []
@@ -1051,6 +1051,11 @@
if not orderby and tablenames:
sql_o += ' ORDER BY %s' % ', '.join(['%s.%s'%(t,x)
for t in tablenames for x in ((hasattr(self.db[t],'_primarykey') and
self.db[t]._primarykey) or [self.db[t]._id.name])])
# oracle does not support limitby
+ # hack to let manually modify query and use the DAL without
executesql
+ sql = attributes.get('sql',None)
+ if sql:
+ return sql
+ # end hack
return self.select_limitby(sql_s, sql_f, sql_t, sql_w, sql_o,
limitby)

def select_limitby(self, sql_s, sql_f, sql_t, sql_w, sql_o,
limitby):

> Is it only a postgress issue? It worked ok with sqlite.

It bombs with mysql (v5.1.49).
The error message is different, but explanation in mysql forums leads
to the same conclusion

InternalError: (1054, u"Unknown column 'second.id' in 'on clause'")

mysql accepts "FROM (second, first)" which fixes the problem
postgres doesn't

>Am I doing something fundamentally wrong or the old issue is not 100% fixed?

I guess I am the only one who is using combination of a right and 2
left joins in one query :-(


--pawel

[Massimo Di Pierro]

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

> REF:http://stackoverflow.com/questions/187146/inner-join-outer-join-is-th...

[Pawel Jasinski]

hi,

somehow I am getting way to many records as a result.

--pawel

[Pawel Jasinski]

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....@gmail.com> wrote: