title: How to build many-to-many relations using SQLAlchemy: a good example
body: <p>I have read the SQLAlchemy documentation and tutorial about building many-to-many relation but I could not figure out how to do it properly when the association table contains more than the 2 foreign keys. </p>
  
  <p>I have a table of items and every item has many details. Details can be the same on many items, so there is a many-to-many relation between items and details</p>
  
  <p>I have the following:</p>
  
  <pre><code>class Item(Base):
      __tablename__ = 'Item'
      id = Column(Integer, primary_key=True)
      name = Column(String(255))
      description = Column(Text)
  
  class Detail(Base):
      __tablename__ = 'Detail'
      id = Column(Integer, primary_key=True)
      name = Column(String)
      value = Column(String)
  </code></pre>
  
  <p>My association table is (It's defined before the other 2 in the code):</p>
  
  <pre><code>class ItemDetail(Base):
      __tablename__ = 'ItemDetail'
      id = Column(Integer, primary_key=True)
      itemId = Column(Integer, ForeignKey('Item.id'))
      detailId = Column(Integer, ForeignKey('Detail.id'))
      endDate = Column(Date)
  </code></pre>
  
  <p>In the documentation, it's said that I need to use the "association object". I could not figure out how to use it properly, since it's mixed declarative with mapper forms and the examples seem not to be complete. I added the line:</p>
  
  <pre><code>details = relation(ItemDetail)
  </code></pre>
  
  <p>as a member of Item class and the line:</p>
  
  <pre><code>itemDetail = relation('Detail')
  </code></pre>
  
  <p>as a member of the association table, as described in the documentation.</p>
  
  <p>when I do item = session.query(Item).first(), the item.details is not a list of Detail objects, but a list of ItemDetail objects. </p>
  
  <p>How can I get details properly in Item objects, i.e., item.details should be a list of Detail objects?</p>