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 ?)
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With