> 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.
ancestors = association_proxy("ancestor_rels", "ancestor")
more demos:
print s.query(DbArea).filter_by(name="Kaymoor").one().ancestors
print s.query(DbArea).filter_by(name="Kaymoor").one().descendents
> The route and area relationship is similar to the hypothetical problem
> of a business location. Suppose you have a set of business locations,
> each in a specific city. The business must be associated with one and
> only one city. The city is located in ever widening areas, e.g., the
> county, region, state, country, planet, etc. Suppose you want to find
> all businesses within a given county, or a state. I'm not sure I
> agree that storing the city id in the business violates normalization,
> since the business can have only one city.
> And I'm not sure that
> storing the business id in every geographical designation (i.e.,
> country, region, state, etc) is a better design. On the contrary, I
> think that you would want to store the city id with the business, and
> the relationship between geographical entities separately.
I was going to suggest relating the DbRoute directly to DbArea, then I noticed that you've actually done this with DbRoute.area_id and DbRoute.area, hadn't noticed that before.
DbRoute.areas asks relationship() to do something impossible - you're asking it to load DbArea objects but then the relationship is forced to not look at any columns that are actually in the DbArea table. You have it linking back to DbRoute.area_id on both sides. Hence it tries to link DbRoute.area_id to DbArea and fails.
>
> As I mentioned in the original post, I can construct an SQL query that
> gets at this relationship (sorry if it got buried):
>
> 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
This query suggests linking route on one side and area on the other, which is more traditional, so you'd just need to link to DbArea.area_id:
areas = relationship('DbArea', backref = 'routes',
secondary = area_relationship_table,
primaryjoin = area_id == area_relationship_table.c.ancestor,
secondaryjoin = DbArea.area_id == area_relationship_table.c.descendent,
innerjoin=True)
This should produce the equivalent idea, an implicit join when lazily loaded and INNER JOIN if joinedload() is used.