title: SQLAlchemy ordered tree structure using association proxy object for positioning
body: <p>I'm trying to create a simple tree structure of tasks. A Task can have a state (done:boolean) and a title (title:string) and any number of subtasks (tasks:Task[]). I've successfully managed to create a Task object and the association proxy object PositionedTask. The parent/child relationship seems to work, allthough the positioning is unused.</p>
  
  <p>This is the code I've got so far:</p>
  
  <pre><code>import sys
  
  from sqlalchemy import Column
  from sqlalchemy import Integer
  from sqlalchemy import String
  from sqlalchemy import Boolean
  from sqlalchemy import ForeignKey
  from sqlalchemy.orm import relationship
  from sqlalchemy.orm import backref
  from sqlalchemy.orm import Session
  from sqlalchemy.ext.associationproxy import association_proxy
  from sqlalchemy.ext.orderinglist import ordering_list
  
  from sqlalchemy import create_engine
  from sqlalchemy.ext.declarative import declarative_base
  
  
  Base = declarative_base()
  
  
  class Task(Base):
      __tablename__ = 'tasks'
  
      id = Column(Integer, primary_key=True)
      title = Column(String, nullable=False)
      done = Column(Boolean, nullable=True, default=False)
  
      parent_id = Column(Integer, ForeignKey('tasks.id'))
      tasks = association_proxy('tasks_positioned', 'task')
  
      def __init__(self, title=None, done=False):
          self.title = title
          self.done = done
  
      def __repr__(self):
          return '&lt;Task #{0} \'{1}\' done={2}&gt;'.format(self.id, self.title, self.done)
  
  
  
  class PositionedTask(Base):
      __tablename__ = 'tasks_subtasks'
      parent_id = Column(Integer, ForeignKey('tasks.id'), primary_key=True)
      task_id = Column(Integer, ForeignKey('tasks.id'), primary_key=True)
      position = Column(Integer, default=0, nullable=False)
  
      parent = relationship(Task, foreign_keys='PositionedTask.parent_id', backref=backref('tasks_positioned', cascade='all, delete-orphan'))
      task = relationship(Task, foreign_keys='PositionedTask.task_id')
  
      def __init__(self, task=None, parent=None, position=None):
          self.task = task
          self.parent = parent
          self.position = position
  
      def __repr__(self):
          return '&lt;PositionedTask parent=\'{0}\' task=\'{1}\' position={2}&gt;'.format(
              self.parent.title, self.task.title, self.position
          )
  
  
  def main(argv=sys.argv):
      engine = create_engine('sqlite:///test.db', echo=False)
      Base.metadata.create_all(engine)
      session = Session(engine)
  
      task = Task('Parent')
      child1 = Task('Child #1', done=True)
      child2 = Task('Child #2')
      grandchild1 = Task('GrandChild #1')
  
      child1.tasks.append(grandchild1)
      task.tasks.append(child1)
      task.tasks.append(child2)
  
      session.add(task)
      session.commit()
  
      task = session.query(Task).filter(Task.id==1).first()
      print task
      for st in task.tasks:
          print st
      for pst in task.tasks_positioned:
          print pst
  
  if __name__ == '__main__':
      main()
  </code></pre>
  
  <p>The output of the current application is:</p>
  
  <pre><code>&lt;Task #1 'Parent' done=False&gt;
  &lt;Task #2 'Child #1' done=True&gt;
  &lt;Task #4 'Child #2' done=False&gt;
  &lt;PositionedTask parent='Parent' task='Child #1' position=0&gt;
  &lt;PositionedTask parent='Parent' task='Child #2' position=0&gt;
  </code></pre>
  
  <p>How can I mix in sqlalchemy.ext.orderinglist.ordering_list to this code to have my subtasks automatically positioned?</p>