On Jan 9, 2012, at 11:58 PM, jonstjohn wrote:
> 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.
--
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.
