problem with selfreference left outer join

[Peter Etchells]

When trying the example in the new slide show:
...db = SQLDB('sqlite://storage.sqlite')

db.define_table('dog',
SQLField('name'),
SQLField('father_id','reference dog'),
SQLField('mother_id','reference dog'))
..
data:

5 selected
dog.id dog.name dog.father_id dog.mother_id
1 biff None None
2 bella None 1
3 fred None 2
4 harry 2 1
5 rover 3 2

def index():
father=db.dog.with_alias('father')
mother=db.dog.with_alias('mother')
rows = db().select(db.dog.name, db.father.name, db.mother.name,
left=(db.father.on(db.father.id==db.dog.father_id),
db.mother.on
(db.mother.id==db.dog.mother_id)))
return dict(rows=rows)

I get the error:
... self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
OperationalError: no such table: LEFT

Any ideas? thanks.

[notabene]

Try list argument in left join:

left =[ ... , ... ]

[mdipierro]

odd. try

print db()._select(db.dog.name, db.father.name, db.mother.name,
left=(db.father.on(db.father.id==db.dog.father_id),
db.mother.on
(db.mother.id==db.dog.mother_id)))

[Peter Etchells]

prints:
SELECT dog.name, dog.name, dog.name FROM LEFT JOIN dog ON
dog.id=dog.father_id LEFT JOIN dog ON dog.id=dog.mother_id;

[mdipierro]

sorry, should have been:

db().select(db.dog.name, father.name, mother.name,
left=(father.on(father.id==db.dog.father_id),
mother.on(mother.id==db.dog.mother_id)))

[DenesL]

Using latest trunk 1014 and starting from empty app the table
definition fails:

db = SQLDB('sqlite://storage.sqlite')
db.define_table('dog',
SQLField('name'),
SQLField('father_id','reference dog'),
SQLField('mother_id','reference dog'))

Traceback (most recent call last):
File "D:\web2py\trunk\gluon\restricted.py", line 107, in restricted
exec ccode in environment
File "D:/web2py/trunk/applications/slides/models/db.py", line 73, in
<module>

SQLField('mother_id','reference dog')

File "D:\web2py\trunk\gluon\sql.py", line 955, in define_table
t = self[tablename] = SQLTable(self, tablename, *fields)
File "D:\web2py\trunk\gluon\sql.py", line 1147, in __init__
raise SyntaxError, 'SQLTable: table does not exist'
SyntaxError: SQLTable: table does not exist

[mdipierro]

Try 1015. It seems the previous commit failed. Works for me.

Massimo

[DenesL]

Same error with 1015.
Did you start from new app?

Steps:
Create new app in admin
Edit db.py to add define_table as above
Click "database administration" in appadmin
Internal error ticket

[DenesL]

This is very easy to reproduce.
Could someone else test and confirm either way?.
Thanks.

[DenesL]

Created a new repository and exported from svn again.
Now it works (!?)
Strange, old gluon repository has all green svn check marks.
Oh well, main thing is it works.

[Peter Etchells]

Thanks, that worked!

On Jun 10, 2:12 am, mdipierro <mdipie...@cs.depaul.edu> wrote: