混合属性是既充当Python属性又充当sql表达式的特殊方法。只要您的difficulty
函数可以用sql表示,它就可以像普通列一样用于过滤和排序。
例如,如果您根据问题的鹦鹉数来计算难度,如果问题超过30天则乘以十,则可以使用:
from datetime import datetime, timedelta
from sqlalchemy import Column, Integer, DateTime, case
from sqlalchemy.ext.hybrid import hybrid_property
class Problem(Base):
parrots = Column(Integer, nullable=False, default=1)
created = Column(DateTime, nullable=False, default=datetime.utcNow)
@hybrid_property
def difficulty(self):
# this getter is used when accessing the property of an instance
if self.created <= (datetime.utcNow() - timedelta(30)):
return self.parrots * 10
return self.parrots
@difficulty.expression
def difficulty(cls):
# this expression is used when querying the model
return case(
[(cls.created <= (datetime.utcNow() - timedelta(30)), cls.parrots * 10)],
else_=cls.parrots
)
并查询:
session.query(Problem).order_by(Problem.difficulty.desc())