Self-referencing table without Foreign-Key SQLAlchemy

[Mene]

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.

Also on stackoverflow, if you prefer:
http://stackoverflow.com/questions/5471687/self-referencing-table-without-foreign-key-sqlalchemy

[Michael Bayer]

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 sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Mene]

Thanks for the answer (and the term "materialized path"). I've seen
the byroot_tree example before, but it uses a parent attribute in the
table like in AL, which is what I strive to avoid.
The descriptor is working, but unfortunately can't be used with e.g.
eagerload, which I'd like to do.

> >http://stackoverflow.com/questions/5471687/self-referencing-table-wit...

[Michael Bayer]

On Mar 31, 2011, at 6:30 AM, Mene wrote:

> Thanks for the answer (and the term "materialized path"). I've seen
> the byroot_tree example before, but it uses a parent attribute in the
> table like in AL, which is what I strive to avoid.
> The descriptor is working, but unfortunately can't be used with e.g.
> eagerload, which I'd like to do.

the eagerload you'd also have to build yourself with a join:

mynode_alias = aliased(MyNode)
session.query(MyNode).outerjoin((mynode_alias, mynode_alias.key.like(MyNode.key + "?"))

I think in 0.4 you'd need to use my_node_table.alias() since it doesn't have aliased().

The mapper extension you have would then have to pull them in and populate. The extension would look almost the same as the one in the byroot_tree example. You'd then need some way to get the descriptor to still be there and load further levels of the tree for those collections that weren't populated on the initial load.