title: Join multiple tables in SQLAlchemy/Flask
body: <p>I am trying to figure out the correct join query setup within SQLAlchemy, but I can't seem to get my head around it.</p>
  
  <p>I have the following table setup (simplified, I left out the non-essential fields):</p>
  
  <pre><code>class Group(db.Model):
      id            = db.Column(db.Integer, primary_key = True)
      number        = db.Column(db.SmallInteger, index = True, unique = True)
      member        = db.relationship('Member', backref = 'groups', lazy = 'dynamic')
  
  class Member(db.Model):
      id            = db.Column(db.Integer, primary_key = True)
      number        = db.Column(db.SmallInteger, index = True)
      groupid       = db.Column(db.Integer, db.ForeignKey('group.id'))
      item          = db.relationship('Item', backref = 'members', lazy = 'dynamic')
  
  class Version(db.Model):
      id           = db.Column(db.Integer, primary_key = True)
      name         = db.Column(db.String(80), index = True)
      items        = db.relationship('Item', backref='versions', lazy='dynamic')  
  
  class Item(db.Model):
      id           = db.Column(db.Integer, primary_key = True)
      member       = db.Column(db.Integer, db.ForeignKey('member.id'))
      version      = db.Column(db.Integer, db.ForeignKey('version.id'))
  </code></pre>
  
  <p>So the relationships are the following:</p>
  
  <ul>
  <li>1:n Group Member</li>
  <li>1:n Member Item</li>
  <li>1:n Version Item</li>
  </ul>
  
  <p>I would like to construct a query by selecting all Item-Rows from the database, that have a certain version. Then I would like to order them by Group and then by Member. The output using Flask/WTForm should look something like this:</p>
  
  <pre><code>* GroupA
    * MemberA
       * ItemA (version = selected by user)
       * ItemB ( dito )
    * Member B
       * ItemC ( dito )
    ....
  </code></pre>
  
  <p>I have come up with something like the following query, but I am pretty sure that it is not correct (and inefficient)</p>
  
  <pre><code>   session.query(Item,Member,Group,Version)
      .join(Member).filter(version.id==1)
      .order_by(Group).order_by(Member).all()
  </code></pre>
  
  <p>My first intuitive approach would have been to create something like</p>
  
  <pre><code>Item.query.join(Member, Item.member==Member.id)
      .filter(Member.versions.name=='MySelection')
      .order_by(Member.number).order_by(Group.number)
  </code></pre>
  
  <p>but obviously, this doesn't work at all. The join operation on the Version table does not seem to produce the type of join between the two tables that I expected. Maybe I am totally misunderstanding the concept, but after reading the tutorials this would have made sense to me.</p>