I'm newish to SQLAlchemy. I've been trying to do something for too
long now so I'm posting here. I'm trying to implement a tree in sql.
I've seen the example here[1] but I prefer to implement trees using
this method[2] (it's cleaner and more elegant).
I'm stuck doing the following join (to get the children in a tree).
How do I reference the table names in a query involving a self-
referential join on a non-primary key. More simply, how do I specify
the bar in 'join foo as bar'?
SELECT node.* (COUNT(parent.name) - 1) as depth
FROM treenodes as node
JOIN treenodes as parent on node.lft BETWEEN parent.lft and
parent.rgt
WHERE node.id = 2
GROUP BY node.id
ORDER BY node.lft
Here is the table definition:
prefix='myprefix_'
schema='mydb'
engine='InnoDB'
treenodes = Table(prefix+'treenodes', meta,
Column('id', Integer, primary_key=True),
Column('lft', Integer, nullable=False),
Column('rgt', Integer, nullable=False),
Column('name', String(50), nullable=False),
mysql_engine=engine,
schema=schema
)
I've tried using the same names (even though I didn't expect it to
work):
mapper(TreeNode, treenodes, properties={
'children' : relation(
TreeNode,
primaryjoin=treenodes.c.lft.between(treenodes.c.lft, treenodes.c.rgt)
) })
But I get the following exception:
sqlalchemy.exceptions.ArgumentError: Can't locate any foreign key
columns in primary join condition 'treenodes.lft BETWEEN treenodes.lft
AND treenodes.rgt' for relationship 'TreeNode.children (TreeNode)'.
Specify 'foreign_keys' argument to indicate which columns in the join
condition are foreign
I added a foreign_keys argument:
foreign_keys=[treenodes.c.lft,treenodes.c.rgt]
And I get a different exception:
sqlalchemy.exceptions.ArgumentError: No syncrules generated for join
criterion treenodes.lft BETWEEN treenodes.lft AND treenodes.rgt
How do I reference the joined table?
Scott
---------------
[1]
http://www.sqlalchemy.org/docs/03/adv_datamapping.html#advdatamapping_selfreferential
[2] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
