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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
