Thanks for persisting with me - your solution was almost perfect, and
really pointing me in the right direction. Below is what eventually
worked:
areas = relationship('DbArea', backref = backref('routes',
order_by = 'DbRoute.name'),
secondary = area_relationship_table,
primaryjoin = area_id == area_relationship_table.c.descendent,
secondaryjoin = DbArea.area_id ==
area_relationship_table.c.ancestor,
innerjoin = True, order_by = DbArea.name,
foreign_keys = [area_relationship_table.c.ancestor,
area_relationship_table.c.descendent])
I swapped your descendent and ancestor, and added the foreign_keys due
a warning that I was receiving 'SAWarning: No ForeignKey objects were
present in secondary table 'area_relationship' ...'. I also added
some sorting for convenience.
This back and forth really helped me out in my work, but also to
better understand SA and particularly SA relationships. A million
thanks!
Jon
On Jan 9, 11:22 pm, Michael Bayer <[email protected]> wrote:
> 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 [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.
