SQLAlchemy and Pyramid, how to get resources out of context?

Question

I'm having a problem wrapping my head around my Pyramid web application. I have it structured very much like the one described by Michael Merickel here, except I'm using pure traversal to find my views. (They are declared configured with context='path.toResource' name='myView'), pretty standard fare according to what I can tell from the traversal wiki tutorial. My application has a more complex URL structure though: My User resources are under /users/{user_id} and my projects are under /projects/{project_id}. All of my resources are persisted using the SQLAlchemy ORM; I have a User and Project class with __name__ and __parent__ attributes, as well as other attributes extending Columns.

class User(Base):
    id = Column(...)
    __name__ = None
    __parent__ = None
Class Project(Base):
    id = Column(...)
    __name__ = None
    __parent__ = None
    owner_id = Column(...ForeignKey(User.id))
    owner = relationship(User,...)

I have a RootFactory, ProjectFactory and UserFactory that populate the appropriate __name__ and __parent__ attributes in their __get_item__ calls.

So, in the view functions for the Project context, I get a Project instance in request.context. My issue is how the heck do I reference the corresponding User instance? I can't do project.owner, because that User instance didn't go through the RootFactory chain, so its __parent__ and __name__ values are unset. This is bad, because I want to use request.resource_url to find the URL for the owner User, so I can put a link on the view page.

What's the solution here SO? Do I do everything through request.root? What if I want to make a complex query that returns User or Project instances? Is there some kind of CrapFactory I can pass SQLAlchemy so all its instances get populated properly?

Is my approach just completely wrong?

I feel like I wouldn't be having these kinds of problems if I just stuck with URL Routing...

Answer 1

This is the issue everyone runs into when trying to mix SQLAlchemy with traversal. A requirement of traversal is having a persistent tree structure that you can traverse to get from one node to another.

You basically have 2 options.

1. You can build an actual tree in your database where each resource knows where it is located. Thus when you load a resource it has some way of determining its parent and name. This is the best option. This is commonly done by creating a self-referential resource table in your database. Then your User and Project objects would both have resource_id foreign keys that they could use to determine where they are at within the tree.

2. Your name and parent are currently only populated when you traverse the tree from the root, so you can do everything through request.root in order to reference other parts of the tree.

It is also possible to just define a __resource_url__ property on each resource but this is fairly hackish as well for most cases.

The thing to take away is that there is a big difference from saying "my urls have this structure" and actually building a persistent hierarchy of that structure that you can use. For handling URLs it's fairly simple, but for generating them it can be painful without a persistent tree.