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.
On 30 Mrz., 21:58, Michael Bayer <mike...@zzzcomputing.com> wrote: > 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-wit... > > > -- > > 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 > > athttp://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.
