On 12/25/08 4:37 PM, Michael Bayer wrote: > On Dec 25, 2008, at 6:45 AM, Wichert Akkerman wrote: > > >> I have a reasonably standard hierarchical datastructure which I'm >> trying >> to store in SQL. The basic model looks like this: >> >> class Page(BaseObject): >> __tablename__ = "page" >> id = schema.Column(types.Integer(), primary_key=True, >> autoincrement=True) >> path = schema.Column(types.Unicode(128), nullable=False, >> index=True) >> children = orm.relation("Page", cascade="all", >> collection_class=attribute_mapped_collection("path")) >> >> This works fine. As shown in the basic_tree example you can configure >> the children relation with eager loading and a join_depth to load >> entire >> tree structure efficiently. >> > > this can't work as stated above. You have a relation() from Page to > itself but no notion of foreign key between the "page" table and > itself is specified. You'll get an error. >
That is mostly due to a copy&paste error. I forgot this line: parent_id = schema.Column(types.Integer(), schema.ForeignKey("page.id", onupdate="CASCADE", ondelete="CASCADE"), index=True) >> I want to do the reverse: build a relation which returns a list of all >> parents of an object. I figured this would work: >> >> parents = orm.relation("Page", remote_side=[id], >> lazy=False, join_depth=5) >> >> That only returns the first parent, not a list of successive >> parents. Is >> it possible to build that parent list like that? >> >> > > if this is a standard adjacency list relation, Page.parent is a many > to one. Joining up five levels will give you a Parent referencing > its Parent referencing its Parent, etc. It did not though: I only got the direct parent. > To roll those up into a > single set of result columns in a single SQL statement would be very > involved - a recursive iterator would be way easier, i.e. > > @property > def parents_iterator(self): > s = self.parent > while s: > yield s > s = s.parent > that suggests that join_depth the other way (recursive children) is also expensive, which the documentation does not mention. Is that correct? Wichert. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---