Can order by work with properties from model?

Question

Can order by work with properties from model ?

class PlayerModel(Base):
    __tablename__ = 'players'
    alliances_id = Column(Integer, ForeignKey('alliances.id'), nullable=True)
    alliance = relationship('AllianceModel')
    username = Column(String(30), nullable=False)
    wins = Column(Integer, default=0, nullable=False)
    defeats = Column(Integer, default=0, nullable=False)

    @property
    def score(self):
        number = self.wins + self.defeats
        if number:
            return self.wins / number
        return 0

in query

 `for player in session.query(PlayerModel).order_by(PlayerModel.wins+PlayerModel.defeats):`

it works but id doesn't work when I put

session.query(PlayerModel).order_by(PlayerModel.wins+PlayerModel.score):`

it reports error expect SQL string. Is possible to sort by property in desc ? (I can load all and sort on application level but it is bad if there is lot of records. Other dirty solution is to have score filed and trigger, but with order and property seems more elegant, is possible ?)

Answer 1

yes, you need to use a feature called hybrid attributes. The expression needs to be stated in terms of both Python logic as well as SQL expression logic:

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy import case

class PlayerModel(Base):
    __tablename__ = 'players'
    alliances_id = Column(Integer, ForeignKey('alliances.id'), nullable=True)
    alliance = relationship('AllianceModel')
    username = Column(String(30), nullable=False)
    wins = Column(Integer, default=0, nullable=False)
    defeats = Column(Integer, default=0, nullable=False)

    @hybrid_property
    def score(self):
        number = self.wins + self.defeats
        if number:
            return self.wins / number
        return 0

    @score.expression
    def score(cls):
        number = cls.wins + cls.defeats
        return case([(number > 0, cls.wins / number)], else_ = 0)