title: Design pattern to organize non-trivial ORM queries?
body: <p>I am developing a web API with 10 tables or so in the backend, with several one-to-many and many-to-many associations. The API essentially is a database wrapper that performs validated updates and conditional queries. It's written in Python, and I use SQLAlchemy for ORM and CherryPy for HTTP handling.</p>
  
  <p>So far I have separated the 30-some queries the API performs into functions of their own, which look like this:</p>
  
  <pre><code># in module "services.inventory"
  def find_inventories(session, user_id, *inventory_ids, **kwargs):
      query = session.query(Inventory, Product)
      query = query.filter_by(user_id=user_id, deleted=False)
      ...
      return query.all()
  
  def find_inventories_by(session, app_id, user_id, by_app_id, by_type, limit, page):
      ....
  
  # in another service module
  def remove_old_goodie(session, app_id, user_id):
      try:
          old = _current_goodie(session, app_id, user_id)
          services.inventory._remove(session, app_id, user_id, [old.id])
      except ServiceException, e:
          # log it and do stuff
  ....
  </code></pre>
  
  <p>The CherryPy request handler calls the query methods, which are scattered across several service modules, as needed. The rationale behind this solution is, since they need to access multiple model classes, they don't belong to individual models, and also these database queries should be separated out from direct handling of API accesses.</p>
  
  <p>I realize that the above code might be called <a href="http://www.refactoring.com/catalog/introduceForeignMethod.html" rel="nofollow noreferrer">Foreign Methods</a> in the realm of refactoring. I could well live with this way of organizing for a while, but as things are starting to look a little messy, I'm looking for a way to refactor this code.</p>
  
  <ul>
  <li>Since the queries are tied directly to the API and its business logic, they are hard to generalize like getters and setters.</li>
  <li>It smells to repeat the <code>session</code> argument like that, but as the current implementation of the API creates a new CherryPy handler instance for each API call and therefore the <code>session</code> object, there is no global way of getting at the current <code>session</code>.</li>
  </ul>
  
  <p>Is there a well-established pattern to organize such queries? Should I stick with the Foreign Methods and just try to unify the function signature (argument ordering, naming conventions etc.)? What would you suggest?</p>