SQLAlchemy: order by columns in chain joined relations
<p>In my app I'v got such models:
</p>

<pre class="lang-python prettyprint-override"><code>class A(Base):
    id = Column(UUID, primary_key=True)
    name_a = Column(Text)

class B(Base):
    id = Column(UUID, primary_key=True)
    name_b = Column(Text)
    parent_a_id = Column(UUID, ForeignKey(A.id))
    parent_a = relationship(A, lazy='joined')

class C(Base):
    id = Column(UUID, primary_key=True)
    name_c = Column(Text)
    parent_b_id = Column(UUID, ForeignKey(B.id))
    parent_b = relationship(B, lazy='joined')
</code></pre>

<p>When I do:</p>

<pre class="lang-python prettyprint-override"><code>DBSession().query(C).all()
</code></pre>

<p>It works fine - I get all C names with it's B and A name in one LEFT OUTER JOIN query.</p>

<p>But I wonder how to <strong>order by</strong> this query with: <strong>name_a, name_b, name_c</strong>?</p>

<p>I'm trying to do something like:</p>

<pre class="lang-python prettyprint-override"><code>DBSession().query(C).order_by(
    C.parent_b.parent_a.name_a,
    C.parent_b.name_b,
    C.name_c
).all()
</code></pre>

<p>Such code doesn't work. How it can be done with sqlalchemy spirit?</p>

<p>I played around with your sample and used PostgreSQL for added points. </p>

<p>Your query returns a list of C objects, and traditionally in SQL you can only sort by columns that you retrieve. The relations are retrieved behind the scenes so I don't see why it should be possible to sort by them. </p>

<p>On the bright side, once your list is retrieved it comes with the entire hierarchy in your memory, so you can sort it in memory super-fast:</p>

<pre><code>clist = session.query(C).all()
sorted(clist, key=lambda c:(c.b.a.name, c.b.name, c.name))
</code></pre>

<p>Another solution I found is to use order_by <code>parameter</code> for <code>relationship</code>:</p>

<pre><code>class B(Base):
   ...
   a = relationship(A, order_by="A.name", lazy='joined')

class C(Base):
   ...
   b = relationship(B, order_by="B.name", lazy='joined')
</code></pre>

<p>End result:</p>

<pre><code>print (session.query(C).order_by(C.name) )
</code></pre>

<blockquote>
  <p>ORDER BY tc.name, tb_1.name, ta_1.name</p>
</blockquote>

<p>This is close to what you wanted but you will need to reverse the list to get it in the <code>A.name, B.name, C.name</code> order.</p>

<p><strong>P.S.:</strong> my exact class definitions for reference:</p>

<pre><code>#!/usr/bin/env python
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, ForeignKey, create_engine, Text
from sqlalchemy.orm import relationship

engine_connect_str = "postgresql://postgres@MYSERV/mydb"
engine = create_engine(engine_connect_str, echo=False)
Session = sessionmaker(engine)
session = Session()
Base = declarative_base()


class A(Base):
    __tablename__ = 'ta'
    id = Column('id', Integer, primary_key=True)
    name = Column(Text)

    def __repr__(self):
        return "{}(id={}, name='{}')".format(self.__class__.__name__, self.id, self.name)

class B(Base):
    __tablename__ = 'tb'    
    id = Column('id', Integer,primary_key=True)
    name = Column(Text)
    a_id = Column('aid', ForeignKey(A.id), nullable=False)

    a = relationship(A, order_by="A.name", lazy='joined')

    def __repr__(self):
        return "{}(id={}, name='{}', a_id={})".format(self.__class__.__name__, self.id, self.name, self.a_id)

class C(Base):
    __tablename__ = 'tc'    
    id = Column('id', Integer, primary_key=True)
    name = Column(Text)
    b_id = Column('bid', ForeignKey(B.id), nullable=False)

    b = relationship(B, order_by="B.name", lazy='joined')

    def __repr__(self):
        return "{}(id={}, name='{}', b_id={})".format(self.__class__.__name__, self.id, self.name, self.b_id)
</code></pre>

python sqlalchemy order sql-order-by relation