title: SQLAlchemy Join Table with a field to order the rows
body: <p>We are migrating from Grails to Python (and Flask). I would like to be able to use the current database structure from our Grails apps in SQLAlchemy.</p>
  
  <p>In Grails I have some domain classes like this:</p>
  
  <pre><code>class Asset {
    static hasMany = [documents:AssetDocument]
    List&lt;AssetDocument&gt; documents = new ArrayList&lt;AssetDocument&gt;();    
  ...
  }
  
  class AssetDocument {
  ...
  }
  </code></pre>
  
  <p>That will create some tables like this in Postgresql</p>
  
  <pre><code>CREATE TABLE asset
  (
    id bigint NOT NULL,
    version bigint NOT NULL,
  ... etc
  )
  
  CREATE TABLE asset_document
  (
    id bigint NOT NULL,
    version bigint NOT NULL,
      .... etc
      )
  </code></pre>
  
  <p>And finally </p>
  
  <pre><code>CREATE TABLE asset_asset_document
  (
    asset_documents_id bigint,
    asset_document_id bigint,
    documents_idx integer,
    CONSTRAINT fk_4pqxhfusa9cevslboxbucsjdn FOREIGN KEY (asset_document_id)
        REFERENCES asset_document (id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
  )
  </code></pre>
  
  <p>The asset_asset_document table joins the Asset table to the Asset_
  document table. </p>
  
  <p>In Grails because I declared the relationship as a List there is an Extra field document_idx this is the Index in the array so for tables like this</p>
  
  <p>asset</p>
  
  <pre><code> +-----+---------+-------+
   | ID  | Version | etc.. |
   +-----+---------+-------+
   |   1 |       0 | ..... |
   |   2 |       0 | ..... |
   +-----+---------+-------+
  </code></pre>
  
  <p>asset_document</p>
  
  <pre><code>+-----+---------+-------+
  | ID  | Version | etc.. |
  +-----+---------+-------+
  |   1 |       0 | ..... |
  |   2 |       0 | ..... |
  |   3 |       0 | ..... |
  +-----+---------+-------+
  </code></pre>
  
  <p>asset_asset_document</p>
  
  <pre><code>+---------------------+--------------------+----------------+
  | asset_documents_id  | asset_document_id  | documents_idx  |
  +---------------------+--------------------+----------------+
  |                   1 |                  1 |              0 |
  |                  1  |                  2 |              1 |
  |                   2 |                  3 |              0 |
  +---------------------+--------------------+----------------+
  </code></pre>
  
  <p>With this example asset where id = 1 has two asset_documents with ids = 1,2
  Where asset id = 2 there is one asset_document with id = 3</p>
  
  <p>The documents_idx field is incremented based on the index in the Array of AssetDocuments.</p>
  
  <p>How do I map this in SQLAlchemy so that if I add an AssetDocument to my Asset relationship the document_idx is updated also ? </p>
  
  <p>For example if I added an AssetDocument to the Asset where id = 2</p>
  
  <pre><code>+---------------------+--------------------+----------------+
  | asset_documents_id  | asset_document_id  | documents_idx  |
  +---------------------+--------------------+----------------+
  |                   1 |                  1 |              0 |
  |                  1  |                  2 |              1 |
  |                   2 |                  3 |              0 |
  |                   2 |                  5 |              1 |
  +---------------------+--------------------+----------------+
  </code></pre>
  
  <p>So far I can see that I need to have</p>
  
  <pre><code>t_asset_asset_document = Table(
      'asset_asset_document', metadata,
      Column('asset_documents_id', BigInteger),
      Column('asset_document_id', ForeignKey(u'asset_document.id')),
      Column('documents_idx', Integer)
  )
  
  class Asset(Base):
      __tablename__ = 'asset'
  
      id = Column(BigInteger, Sequence('seq_asset'), primary_key=True)
      version = Column(BigInteger, nullable=False)
      documents = relationship("AssetDocument",
                      secondary=t_asset_asset_document) #What needs to be here??
  
  class AssetDocument(Base):
      __tablename__ = 'asset_document'
  
      id = Column(BigInteger, Sequence('seq_asset_document'), primary_key=True)
      version = Column(BigInteger, nullable=False)
  </code></pre>