On Mar 30, 2011, at 2:58 PM, Mene wrote:
> Hello, often when you have a table with selfrefernece, say in a tree,
> you have an attribute as a foreign key, which is pointing to the
> primary key of the table. (like in the Adjacency List Relationships
> example from the docs)
>
> However, I have a natural key, which works like this:
> "" is the root
> "a", "b", "c" etc. are the children of ""
> "aa", "ab", "ac" etc. are the children of "a"
> "ba", "bb", "bc" etc. are the children of "b"
> "aaa", "aab", "aac" etc. are the children of "aa"
>
> So there is one character for each level in the tree and the child
> nodes of each node are those with the same beginning and one character
> added.
>
> How would I do this using an SQLAlchemy mapper without adding an extra
> attribute to reference the parent?
>
> Note: I'm only interested in reading the relation ship, if something
> like node.children.append(child) isn't working thats fine. Also I'm
> stuck to version 0.4.8, however if this is not possible in this
> version but only in a newer one I might take the effort to update.
this is called "materialized path" in SQL parlance and you'd need to forego the
usage of relation() in favor of a descriptor that loads the records you want:
class MyNode(object):
@property
def children(self):
return
object_session(self).query(MyNode).filter(MyNode.key.like(self.key + "?")).all()
there are also strategies by which you can use MapperExtension to intercept the
point at which rows are appended to the result list, and instead build the
"child" structure at that point. There is an old example (but you're on 0.4
anyway) in the 0.4 dist called examples/adjacencytree/byroot_tree.py which does
this.
>
> Also on stackoverflow, if you prefer:
> http://stackoverflow.com/questions/5471687/self-referencing-table-without-foreign-key-sqlalchemy
>
> --
> 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
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
