Michael -
I greatly appreciate the time and consideration you put into your
thorough reply. It has really helped me better understand how SQL
Alchemy handles associations. In particular, it is now apparent to me
that there is no clear association between area and route, which makes
it not possible to map through. Although I think I completely
understand your explanation and sample code, it might be helpful for
me to clarify the problem with a simple example in a common problem
domain.
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.
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
However, I still can't figure out how to create these mappings in SQL
Alchemy.
As a work-around, I queried for the descendents first, then filtered
with an 'in' for all descendents. This works fine for me since I only
have several levels of nesting, but would probably get inefficient if
I had deeper nestings (and thus a potentially very large 'in'
condition).
Work around:
def get_routes(area_id):
# do imports, init session, etc
area_ids = []
area = session.query(DbArea).filter(DbArea.area_id ==
area_id).one()
for descendent in area.descendents:
area_ids.append(descendent.area_id)
return
session.query(DbRoute).filter(DbRoute.area_id.in_(area_ids))
Let me know if you see a way to accomplish this w/o doing the
intermediate query for descendents and the 'in' condition.
Thanks again!
Jon
On Jan 9, 5:33 pm, Michael Bayer <[email protected]> wrote:
> typo in the DbArea.ancestors attribute:
>
> 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
--
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.
