title: SQLAlchemy and Pyramid, how to get resources out of context?
body: <p>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 <a href="https://stackoverflow.com/questions/5761617/pyramid-authorization-for-stored-items/5761901#5761901">here</a>, 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 <a href="http://docs.pylonsproject.org/projects/pyramid/en/1.2-branch/tutorials/wiki/index.html#bfg-wiki-tutorial" rel="nofollow noreferrer">traversal wiki tutorial.</a> My application has a more complex URL structure though: My User resources are under <code>/users/{user_id}</code> and my projects are under <code>/projects/{project_id}</code>. All of my resources are persisted using the SQLAlchemy ORM; I have a User and Project class with <code>__name__</code> and <code>__parent__</code> attributes, as well as other attributes extending Columns. </p>
  
  <pre><code>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,...)
  </code></pre>
  
  <p>I have a RootFactory, ProjectFactory and UserFactory that populate the appropriate <code>__name__</code> and <code>__parent__</code> attributes in their <code>__get_item__</code> calls.</p>
  
  <p>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 <code>__parent__</code> and <code>__name__</code> 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.</p>
  
  <p>What's the solution here SO? Do I do <em>everything</em> 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? </p>
  
  <p>Is my approach just completely wrong?</p>
  
  <p>I feel like I wouldn't be having these kinds of problems if I just stuck with URL Routing...</p>