On Jan 9, 2012, at 6:51 PM, jonstjohn wrote:
> To implement this I chose to use a closure table ala Bill Karwin
> (http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-
> tables.html). In the closure table implementation, a second table is
> created to store the ancestor/descendent information. A self-
> referencing row is created when a node is added, as well as a row for
> each ancestor in the tree.
This is a schema design I've not seen before. What's unusual here is that
you'd like the "route" table to have something to do with the
"area_relationship" table, however "area_relationship" has no foreign key to
"route", nor vice versa. This doesn't follow typical normalization rules,
in that it's very easy to have rows in DbRoute that don't exist in
area_relationship_table. SQLAlchemy's ORM can't really navigate around a
design like that and it's probably not really what you want here.
I checked the blog post you refer to and it does not have this pattern. It has
just a "node" table, which here corresponds to DbArea, and "closure", which
here would be "area_relationship_table" and in SQLA we call it a
self-referential many-to-many. There's no "extra" table, so we need to figure
out what you want there.
If it's the case that information such as "Leave it to Jesus", "Green Peace"
are associated with a particular association between two areas, you would first
need to promote area_relationship to be a fully mapped class, forming what we
refer to as an "association object"
(http://www.sqlalchemy.org/docs/orm/relationships.html#association-object) - a
many-to-many table that contains additional information about the association.
Based on how you've named things here I think you'd then want to foreign key
that table to DbRoute, so that DbRoute contains a collection of
AreaRelationship associations. You could also do something simpler which is
just to add a string column to the AreaRelationship association table directly.
Introducing the association object usually leads to the usage of the
association proxy to "hide" the middle object in most cases. As this is
likely to be a lot to take in, and there's also some complexity in getting the
"routes" for an area, the attached script illustrates a mapping that seems to
correspond to the sample data you have.
>
>
> The table structure is as follows (simplified):
>
> -- area --
> area_id
> name
>
> -- area_relationship --
> ancestor
> descendent
>
> -- route --
> route_id
> area_id
> name
>
>
> Sample data:
>
> -- area --
> 1, New River Gorge
> 2, Kaymoor
> 3, South Nuttall
> 4, Meadow River Gorge
>
> -- area_relationship (ancestor, descendent) --
> 1, 1 (self-referencing)
> 2, 2 (self-referencing)
> 1, 2 (Kaymoor is w/i New River Gorge)
> 3, 3 (self-referencing)
> 1, 3 (South Nutall is w/i New River Gorge)
> 4, 4 (self-referencing)
>
> -- route (route_id, area_id, name)
> 1, 2, Leave it to Jesus
> 2, 2, Green Piece
> 3, 4, Fancy Pants
>
>
> To query for all areas for a given route (up the tree), I can execute:
>
> SELECT area.area_id, area.name
> FROM route
> INNER JOIN area_relationship ON route.area_id =
> area_relationship.descendent
> INNER JOIN area ON area.area_id = area_relationship.ancestor
> WHERE route.route_id = 1
> Similarly, I can query for all routes in a particular area (including
> descendent areas) with:
>
> SELECT route.route_id, route.name
> FROM area
> INNER JOIN area_relationship ON area.area_id =
> area_relationship.ancestor
> INNER JOIN route ON route.area_id = area_relationship.descendent
> WHERE area.area_id = 1
>
>
> In SQL Alchemy I've created a relationship and two tables to handle
> these relationships:
>
> area_relationship_table = Table('area_relationship', Base.metadata,
> Column('ancestor', Integer, ForeignKey('area.area_id')),
> Column('descendent', Integer, ForeignKey('area.area_id'))
> )
>
>
> DbArea class -
>
> class DbArea(Base):
>
> __tablename__ = 'area'
>
> area_id = Column(Integer, primary_key = True)
> name = Column(VARCHAR(50))
> created = Column(DATETIME)
>
> area_relationship_table.c.ancestor])
>
> descendents = relationship('DbArea', backref = 'ancestors',
> secondary = area_relationship_table,
> primaryjoin = area_id == area_relationship_table.c.ancestor,
> secondaryjoin = area_id ==
> area_relationship_table.c.descendent)
>
>
> DbRoute class -
>
> class DbRoute(Base):
>
> __tablename__ = 'route'
>
> route_id = Column(Integer, primary_key = True)
> area_id = Column(Integer, ForeignKey('area.area_id'))
> name = Column(VARCHAR(50))
> created = Column(DATETIME)
>
> area = relationship("DbArea")
>
> areas = relationship('DbArea', backref = 'routes',
> secondary = area_relationship_table,
> primaryjoin = area_id ==
> area_relationship_table.c.ancestor,
> secondaryjoin = area_id ==
> area_relationship_table.c.descendent,
> foreign_keys=[area_relationship_table.c.ancestor,
> area_relationship_table.c.descendent])
>
>
> Currently, I am able to determine the areas from the individual route,
> using the areas relationship in DbRoute. However, when I try to use
> the backref 'routes' in DbArea, I get the following error:
>
> sqlalchemy.exc.StatementError: No column route.area_id is configured
> on mapper Mapper|DbArea|area... (original cause: UnmappedColumnError:
> No column route.area_id is configured on mapper Mapper|DbArea|area...)
> 'SELECT route.route_id AS route_route_id, route.area_id AS
> route_area_id, route.name AS route_name, route.created AS
> route_created \nFROM route, area_relationship \nWHERE %s =
> area_relationship.descendent AND route.area_id =
> area_relationship.ancestor' [immutabledict({})]
>
> I'm guessing that I likely need to add something to DbArea to
> establish the relationship, but after experimenting with some
> different options was unable to determine the solution.
>
> --
> 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.
>
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
Base= declarative_base()
class DbArea(Base):
__tablename__ = 'area'
area_id = Column(Integer, primary_key = True)
name = Column(VARCHAR(50))
created = Column(DATETIME)
descendent_rels = relationship('AreaRelationship', backref = 'ancestor',
primaryjoin = "DbArea.area_id == AreaRelationship.ancestor_id")
ancestor_rels = relationship('AreaRelationship', backref='descendent',
primaryjoin = "DbArea.area_id == AreaRelationship.descendent_id")
ancestors = association_proxy("anscestor_rels", "ancestor")
descendents = association_proxy("descendent_rels", "descendent")
@property
def routes(self):
return [ar.route for ar in self.ancestor_rels] + [
ar.route for ar in self.descendent_rels
]
class AreaRelationship(Base):
__tablename__ = 'area_relationship'
ancestor_id = Column(Integer, ForeignKey('area.area_id'), primary_key=True)
descendent_id = Column(Integer, ForeignKey('area.area_id'), primary_key=True)
route_id = Column(Integer, ForeignKey('route.route_id'))
route = relationship("DbRoute", backref="area_rels")
class DbRoute(Base):
__tablename__ = 'route'
route_id = Column(Integer, primary_key = True)
name = Column(VARCHAR(50))
@property
def areas(self):
return [ar.ancestor for ar in self.area_rels] + [
ar.descendent for ar in self.area_rels
]
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
a1, a2, a3, a4 = DbArea(name="New River Gorge"),\
DbArea(name="Kaymoor"),\
DbArea(name="South Nuttall"),\
DbArea(name="Meadow River Gorge")
r1, r2, r3 = DbRoute(name="Leave it to Jesus"), DbRoute(name="Green Piece"), DbRoute(name="Fancy Pants")
s.add_all([
AreaRelationship(ancestor=a1, descendent=a1),
AreaRelationship(ancestor=a2, descendent=a2, route=r2),
AreaRelationship(ancestor=a1, descendent=a2, route=r1),
AreaRelationship(ancestor=a3, descendent=a3),
AreaRelationship(ancestor=a1, descendent=a3),
])
s.commit()
print s.query(DbRoute).filter_by(name="Green Piece").one().areas
--
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.
