title: sqlalchemy join and order by on multiple tables
body: <p>I'm working with a database that has a relationship that looks like:</p>
  
  <pre><code>    class Source(Model):
         id = Identifier()
  
      class SourceA(Source):
         source_id = ForeignKey('source.id', nullable=False, primary_key=True)
         name = Text(nullable=False)
  
      class SourceB(Source):
         source_id = ForeignKey('source.id', nullable=False, primary_key=True)
         name = Text(nullable=False)
  
      class SourceC(Source, ServerOptions):
         source_id = ForeignKey('source.id', nullable=False, primary_key=True)
         name = Text(nullable=False)
  </code></pre>
  
  <p>What I want to do is join all tables Source, SourceA, SourceB, SourceC and then <code>order_by</code> name.</p>
  
  <p>Sound easy to me but I've been banging my head on this for while now and my heads starting to hurt. Also I'm not very familiar with SQL or sqlalchemy so there's been a lot of browsing the docs but to no avail. Maybe I'm just not seeing it. <a href="http://docs.sqlalchemy.org/en/rel_0_9/orm/join_conditions.html#composite-secondary-joins" rel="nofollow">This</a> seems to be close albeit related to a newer version than what I have available (see versions below).</p>
  
  <p>I feel close not that <em>that</em> means anything. Here's my latest attempt which seems good up until the <code>order_by</code> call.</p>
  
  <pre><code>    Sources = [SourceA,  SourceB, SourceC]
      # list of join on Source
      joins = [session.query(Source).join(source) for source in Sources]
      # union the list of joins
      query = joins.pop(0).union_all(*joins)
  </code></pre>
  
  <p>query seems right at this point as far as I can tell i.e. <code>query.all()</code> works. So now I try to apply <code>order_by</code> which doesn't throw an error until <code>.all</code> is called.</p>
  
  <p>Attempt 1: I just use the attribute I want</p>
  
  <pre><code>    query.order_by('name').all()
      # throws sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "name" does not exist
  </code></pre>
  
  <p>Attempt 2: I just use the defined column attribute I want        </p>
  
  <pre><code>    query.order_by(SourceA.name).all()
      # throws sqlalchemy.exc.ProgrammingError: (ProgrammingError) missing FROM-clause entry for table "SourceA"
  </code></pre>
  
  <p>Is it obvious? What am I missing? Thanks!</p>
  
  <p>versions:</p>
  
  <p>sqlalchemy.<strong>version</strong> = '0.8.1'</p>
  
  <p>(PostgreSQL) 9.1.3</p>
  
  <p><strong>EDIT</strong>
  I'm dealing with a framework that wants a handle to a query object. I have a bare query that appears to accomplish what I want but I would still need to wrap it in a query object. Not sure if that's possible. Googling ...</p>
  
  <pre><code>select = """
      select s.*, a.name from Source d inner join SourceA a on s.id = a.Source_id
      union
      select s.*, b.name from Source d inner join SourceB b on s.id = b.Source_id
      union
      select s.*, c.name from Source d inner join SourceC c on s.id = c.Source_id
      ORDER BY "name";
  """
  selectText = text(select)
  result = session.execute(selectText)
  # how to put result into a query. maybe Query(selectText)? googling...
  result.fetchall():
  </code></pre>