SQLAlchemy: How to order query results (order_by) on a relationship's field?

Question

Models

from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, ForeignKey from sqlalchemy import Integer from sqlalchemy import Unicode from sqlalchemy import TIMESTAMP from sqlalchemy.orm import relationship  BaseModel = declarative_base()  class Base(BaseModel):    __tablename__ = 'base'    id = Column(Integer, primary_key=True)    location = Column(Unicode(12), ForeignKey("locationterrain.location"), unique=True,)    name = Column(Unicode(45))    ownerid =  Column(Integer,ForeignKey("player.id"))    occupierid =  Column(Integer, ForeignKey("player.id"))    submitid =  Column(Integer,ForeignKey("player.id"))    updateid =  Column(Integer,ForeignKey("player.id"))    owner = relationship("Player",          primaryjoin='Base.ownerid==Player.id',          join_depth=3,          lazy='joined')    occupier= relationship("Player",          primaryjoin='Base.occupierid==Player.id',          join_depth=3,          lazy='joined')    submitter = relationship("Player",          primaryjoin='Base.submitid== Player.id',          join_depth=3,          lazy='joined')    updater= relationship("Player",          primaryjoin='Base.updateid== Player.id',          join_depth=3,          lazy='joined')   class Player(BaseModel):    __tablename__ = 'player'    id = Column(Integer, ForeignKey("guildmember.playerid"), primary_key=True)    name =  Column(Unicode(45)) 

Searching

bases = dbsession.query(Base) bases = bases.order_by(Base.owner.name) 

This doesn't work .... I've searched everywhere and read the documentation. But I just don't see how I can sort my (Base) query on their 'owner' relationship's name.

It always results in:

 AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'name' 

This must be easy... but I don't see it. Also looked into Comparators, which seemed logical, but I don't see where the query part for the ORDER BY is generated or what I should be returning since everything is generated dynamically. And making a comparator for each of my 'player' relationships to do a simple thing seems over complicated.

Answer 1

SQLAlchemy wants you to think in terms of SQL. If you do a query for "Base", that's:

SELECT * FROM base 

easy. So how, in SQL, would you select the rows from "base" and order by the "name" column in a totally different table, that is, "player"? You use a join:

SELECT base.* FROM base JOIN player ON base.ownerid=player.id ORDER BY player.name 

SQLAlchemy has you use the identical thought process - you join():

session.query(Base).join(Base.owner).order_by(Player.name)