Navigate through tree-like structure with sqlalchemy. Is it doable (now) ?

[Hector Blanco]

Hello everyone:

I have a tree-like structure (groups/nodes, basically) with Stores and
StoreGroups. An store can belong only to one storeGroup, but an
StoreGroup can contain stores or other storeGroups:

class StoreGroup(BaseClass.BaseClass, Database.Base):
"""Represents a storeGroup"""
__tablename__ = "store_groups"
_id = Column("id", Integer, primary_key=True)
_name = Column("name", String(50))

_storeGroups = relationship("StoreGroup",
secondary=store_group_groups, order_by=lambda:StoreGroup.name,
primaryjoin=lambda: StoreGroup.id == store_group_groups.c.store_groupA_id,
secondaryjoin=lambda: StoreGroup.id == store_group_groups.c.store_groupB_id,
backref="parentGroup",
collection_class=set)
#_stores > Backref from "Store" class

class Store(BaseClass.BaseClass, Database.Base):
"""Represents a store"""
__tablename__ = "stores"
_id = Column("id", Integer, primary_key=True)
_name = Column("name", String(50))
_number = Column("number", Integer)
_timeZone = Column("time_zone", String(20))

_storeGroupId = Column("store_group_id", Integer,
ForeignKey("store_groups.id"))
_storeGroup = relationship("StoreGroup", uselist=False,
backref=backref("_stores",
order_by=lambda:Store.name,
collection_class=set)
)

So I would like to have a way to, given an "StoreGroup" id, to
recursively iterate through the hierarchy (StoreGroup.storeGroups) and
grab all the "Stores" found on said hierarchy.

I have seen the example:
http://www.sqlalchemy.org/trac/browser/examples/adjacency_list/adjacency_list.py

but that needs the depth to look in in advance.

I also saw:
http://groups.google.com/group/sqlalchemy/msg/80ea8e712380bff4

where apparently there's no "sqlalchemistic" way of doing it (it needs
sql tools and raw queries). I was hoping that maaaaybe the information
I have is outdated, and it's doable now (using SqlAlchemy 0.6.6)

Thank you.

[Michael Bayer]

that link doesn't appear to be relevant to what you are asking here. To iterate as you asked, that would be:

group = session.query(StoreGroup).filter(StoreGroup._id==id).one()

stack = [group]
while stack:
g = stack.pop()
for store in g._stores:
do_something_with(store)
stack.extend(g._storeGroups)

[Hector Blanco]

Thanks for the quick reply.

I'll give it a try.

2011/4/8 Michael Bayer <mik...@zzzcomputing.com>:

> --
> 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.
>
>